New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
How can I optimize flat table reads in SQLSERVER 2012?
I have a flat table with close to 800k+ rows. on Sqlsever, it takes around 4 minutes to read the whole data-set using select * (there are around 40 columns).
How can I boost the reads? I have a simple clustered index on the id coloumn. I read about ColumnStore indexes, but, adding those did not boost the performance much (we added the columnstore on all columns).
Any thoughts?
Comments
One what occasions woukd you read the whole dataset?
Use perfom and resource monitor to quickly find the slowest part and then you know where to tweak
I think you bottle neck is I/O, select *, no join; basically sql server dump everything to you.
How big is your data set? 40 column, assume each column 16 bytes, 800k, roughly 512M, it is nothing. You either have a slow disk or a slow network, or both.
Does this simple SELECT * have an ORDER BY?
And I'm with @mikho ... why do you need to do this? Can you implement some sort of paging and only select small chunks at a time?
I noticed that I made a typo in my previous post.
The software to use is perfmon, nothing else. I am also curious if this is the Express edition or a licensed version?
It doesn't really matter whether its a clustered index or a plain heap table if the result set is all table columns and all rows, because all data making up the table is eventually going to be returned, this is how
select * from tableworks. And it is true for any RDBMs, be it SQL Server or Oracle Database or MySQL etc.You should ask yourself a question: Do you really want all the rows (and columns) to be returned or only a subset of them?
If you don't, then you could make your query more selective with WHERE clause and this is where performance tuning comes into play. If you do, well, this is only a matter of your I/O throughput (if you don't employ sorting).
@Ree mentioned sorting with the ORDER BY clause, but if your table is the clustered index and you want the rows to be sorted by the clustering key (id), the rows are already in particular order, and the database engine doesn't need to do the extra work to sort them, it just scans the pages in order and returns the rows.
If you sort by any other column(s) and/or conditions in your queries specify only a subset of columns, then you probably want to add more (possible composite) indexes. This way the optimizer can build more effective query plan and the database knows (with the help of indexes) in which order it needs to read the pages to satisfy the sorting condition and/or which pages it needs to read to get the rows satisfying the query condition.
You can ask your question on Q&A site http://dba.stackexchange.com/
@ all.
Thank you for your responses.
One of the common questions coming is: Why do I need a select *?
The views (which currently composes the flat-table data) is taking an obnoxiously long time to execute. We are trying to optimize the same. However, still, in the interim, we found loading data in a flat table and then reading that from our BI app should take the load time from 10 mins to a couple of minutes.
Looks like doing a flat read is not going to help speed up the reads from our BI app afterall.
Asking about slow reads from SQL Server and a business intelligence application on a forum dedicated to $7/mo VPSes...
Where else do you find people so specialized in optimization?
SO
Always open for ideas from all.
What kind of hardware are you running on?