February 17, 2015 at 9:14 am
Hi all, I am writing a query that hits a table with 1.5 billion rows. After about a minute I get back about 28k of 60k then the query runs pretty much one row at a time and takes 15 minutes to pull up the remaining 32k. I am assuming this is probably an index issue, as our tables are kind of a mess and while they have indexes not exactly sure they know why and have certianly never done a rebuild. Has anyone run into this before? Mahalo. Below are the stats the large table is clinical_event the last one.
Table 'Encounter'. Scan count 853, logical reads 55465, physical reads 5, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Diagnosis'. Scan count 7373, logical reads 45236, physical reads 138, read-ahead reads 1686, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Nomenclature'. Scan count 886, logical reads 252, physical reads 102, read-ahead reads 70, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Encntr_Acct_Xref'. Scan count 20867, logical reads 83799, physical reads 5701, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ACCT_DIA'. Scan count 31, logical reads 439267, physical reads 0, read-ahead reads 435970, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Encntr_Alias'. Scan count 1706, logical reads 91786, physical reads 397, read-ahead reads 27117, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Clinical_Event'. Scan count 12819900, logical reads 47600206, physical reads 939737, read-ahead reads 1117972, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
February 17, 2015 at 9:41 am
mittensonmaui (2/17/2015)
Hi all, I am writing a query that hits a table with 1.5 billion rows. After about a minute I get back about 28k of 60k then the query runs pretty much one row at a time and takes 15 minutes to pull up the remaining 32k. I am assuming this is probably an index issue, as our tables are kind of a mess and while they have indexes not exactly sure they know why and have certianly never done a rebuild. Has anyone run into this before? Mahalo.
Hi and welcome to the forums. There could be a zillion reasons why the performance is poor here. Without a lot more details it is guess work. Take a look at this article for how to post performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2015 at 11:28 am
Without the execution plan, nothing but vague guesses are possible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2015 at 11:30 am
Unfortunately, they do not give us access to that which I don't understand.
February 17, 2015 at 11:44 am
My experience with huge queries is that they run linearly but SSMS gets result batches in a non-linear fasion. For example, it may show 10 result rows at a time for the first hundred, then switch to getting 100 result rows at a time for the next 900 which of course has 10 times longer delays between screen updates.
Also your read ahead reads and physical reads are astronomical. Data cache must be turning over and over to service one query...depending on cache size.
February 17, 2015 at 12:01 pm
mittensonmaui (2/17/2015)
Unfortunately, they do not give us access to that which I don't understand.
Tell "them" to make sure an index is present that matches the query criteria. Otherwise, the query will have to read every one of those 1.5 billion rows to see if it meets the criteria given by the query text.
February 17, 2015 at 12:30 pm
Then all I can suggest is the standard stuff. Make sure you're queries are filtering on criteria where there are indexes. Add indexes if necessary. Update the statistics, possibly using FULL SCAN. Make sure you don't have any common code smells such as functions on columns in WHERE/HAVING/ON, avoid implicit data conversions because they're the same thing as a function, avoid nested views, avoid multi-statement table valued user defined functions altogether. For lots more details, see the books in my signature below.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2015 at 12:41 pm
Thx guys I appreciate your input 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply