Slow Query on Large Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow Query on Large Table

Author

Topic

Khalil
Starting Member

6 Posts

Posted - 2015-03-10 : 05:14:06

Hi,

I have a query that runs very slow on a large table (1 million records) where it has to retrieve all records with dateX>@today, after dateX is a newly added field to the table with a default value of '1/1/1900' and NULL not allowed.

However, if I later tick "Allow Nulls" and get "Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible." and proceed, the query runs fast afterwards. If I then untick "Allow Nulls" and proceed, the query still runs fast.

While this resolves my problem, I would like to understand what is going on :)

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-10 : 07:36:42

Can you show us the execution plan ?
I guess , that in first case is doing a something like a nested loop + bookmark look ups instead of a table scan. This is because statistics are old.
When you are doing the alter stmt , this modifies the rows which trigger an update on statistics. Now, the optimizer can fully benefit on statistics
and choose the right execution plan .

sabinWeb MCP

Khalil
Starting Member

6 Posts

Posted - 2015-03-10 : 10:28:38

Hi,

Many thanks stepson. I am not familiar with execution plan and statistics but this would make sense...I just want to ask you what is the proper way to update statistics (instead of dummy alter stmt)?

Thank you

quote:
Originally posted by stepson

Can you show us the execution plan ?
I guess , that in first case is doing a something like a nested loop + bookmark look ups instead of a table scan. This is because statistics are old.
When you are doing the alter stmt , this modifies the rows which trigger an update on statistics. Now, the optimizer can fully benefit on statistics
and choose the right execution plan .

sabinWeb MCP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-10 : 10:45:39

Checking "Allow Null" just changes the metadata.
Unchecking the "Allow Null" forces SQL Server to check the entire table to see if there are rows that violate your new wish.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-10 : 10:48:24

Also, sometimes SSMS generates code that is not necessary, like creating a new table, copy all data to the new table, drop the old table and rename the new table. For no reason at all.
Just click "See change script" to see what SSMS is doing. What SSMS is doing, has nothing to do with SQL Server and performance.

If you want to be a good administrator/developer, only use t-sql commands like

ALTER TABLE dbo.Table1 ALTER COLUMN Column1 INT NOT NULL; -- To not allow null

or

ALTER TABLE dbo.Table1 ALTER COLUMN Column1 INT NULL; -- To allow null

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Khalil
Starting Member

6 Posts

Posted - 2015-03-10 : 10:49:08

Thanks SwePeso; so I am trying to understand how/why changing the metadata causes the query to run very much faster, and asking if there is a better way to do this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-10 : 11:43:03

Maybe because the table is recreated, the statistics is updated and thus a better plan is produced?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Khalil
Starting Member

6 Posts

Posted - 2015-03-12 : 07:44:57

Hi All,

Thanks for your answers; I tried to update statistics with a similar issue using (USE MyDB; GO EXEC sp_updatestats) but it didn't improve performance.
Let me try to be more specific and ask for your help again; consider the 2 queries:
Query1: select * from Table1 where Date1>getdate() and Condition2
Query2: select * from Table1 where Date2>getdate() and Condition2
Condition 2 is the same in both and results alone in a count of about 1 million rows
Date1>getdate() alone results in about 20,000 rows
Date2>getdate() alone results in about 200 rows
Query1 results in 18000 rows and runs for about 1 second only
Query2 results in 0 rows and runs for over 30 seconds
Execution plans are different too; Any idea how to improve the performance of query 2? Somehow make it apply the same execution plan as Query1???
Thanks

Khalil

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-12 : 08:33:30

Do you have indexes ? Are they fragmented ? (see sys.dm_db_index_physical_stats)

sabinWeb MCP

Khalil
Starting Member

6 Posts

Posted - 2015-03-15 : 06:37:42

Thanks for following up stepson.

Kindly see below (Date condition being on the Main table, while condition 2 involves sub tables)

Main Table CLUSTERED INDEX IN_ROW_DATA 3 0 0.01 767 50.57757497 38793
Sub Table CLUSTERED INDEX IN_ROW_DATA 3 0 97.36324763 88488 1.017482597 90035
Sub Sub Table CLUSTERED INDEX IN_ROW_DATA 3 0 4.008416623 810 23.4691358 19010
Sub Sub Table CLUSTERED INDEX IN_ROW_DATA 3 0 99.19216226 5818 1 5818

I am not familiar with execution plan but trying to see a difference, it looks like fast query includes some "Hash Match", while slow one does not.
Could it explain? If yes, why isn't slow query including "Hash Match"?
By the way, I am running the queries from C# SlqCommand.ExecuteScalar, looping through dates (date1, date2...); I am only using SSMS for debugging timeout error.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-15 : 07:29:06

Time to get familiar wxith execution plans!

Khalil
Starting Member

6 Posts

Posted - 2015-03-23 : 12:00:47

Was hoping for an easy answer ;) thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 12:13:00

http://www.sqlservercentral.com/articles/105771/