August 27, 2012 at 6:59 pm
The select statement without hints and the select statement without sorting or hints both ran slightly faster than the original one. The new query that you sent me ran a little bit slower than the original one without hints. Maybe the optimizer didn't do the right things with it. I have been running each query several times because the results vary quite a bit depending on whether I have run the same query already or not, due to caching I guess. So the original query without hints took 5:04 to run the first time, 3:17 to run the second time, and 0:35 to run the third time. The new query took 6:46 the first time, 3:52 the second time, and 0:34 the third time. Is there any way to "prime" the cache prior to executing a query other than by running the query? Since I am only running the query once in production, the times I am getting are more like the slowest times that I get in these timing tests.
Also, I am running these tests using data sets that have about 1,000 patients and 130,000 main documents because if I run them with larger data sets in SQL Server Management Studio, the C: drive of the machine I am using runs out of disk space before the tests complete. SQL Server Management Studio is installed on the C: drive, which is chronically almost full, but it has a D: drive with a lot of free space. Is there a way to tell SQL Server Management Studio to use a different drive for whatever files it is saving away while a query is being processed?
The execution plan for the new query is attached.
Also, the VDiagnosis table contains 1,433,074,939 rows.
Thanks,
Mike
August 28, 2012 at 8:03 am
tmoleary (8/27/2012)
Also, I am running these tests using data sets that have about 1,000 patients and 130,000 main documents because if I run them with larger data sets in SQL Server Management Studio, the C: drive of the machine I am using runs out of disk space before the tests complete. SQL Server Management Studio is installed on the C: drive, which is chronically almost full, but it has a D: drive with a lot of free space. Is there a way to tell SQL Server Management Studio to use a different drive for whatever files it is saving away while a query is being processed?
The only thing of any size that Management Studio (SSMS) holds/saves is the query output. if that's your problem, then redirect the output to a temp table with SELECT INTO for your tests.
SQL Server itself, is another story. I'd expect your tempdb to be used heavily for the sorting, so if your SQL Server instance is on the same system, then you could look at moving it to your D: drive.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply