March 1, 2010 at 12:42 pm
I have 2 similar databases with same structure execpt that databaseA is 300GB and databaseB is 1.4TB but when i exec a similar query on both databases, A takes 21 min and B takes only 45sec, why is it so ? its kind of reverse, i was expecting bigger database to take longer time but not.
A and B are completely identical exepect the size.
March 1, 2010 at 1:27 pm
March 1, 2010 at 1:36 pm
I am not doubting the query but looking for exact reason why same query performs diffrent way on both databases which are similar except the size.
March 1, 2010 at 2:13 pm
I am not looking to tune the query but would like to dignosis the issue why same query can run faster on larger database compared to smaller database.
March 1, 2010 at 2:14 pm
indexes.. statistics... could be one of the reasons thats why i asked for execution plans
March 1, 2010 at 2:29 pm
and i am not looking to optimize your query but looking at the execution plan details will help to reveal and understand the different behaviour
March 1, 2010 at 2:35 pm
steve
what format of execution plan you are looking into ?
March 1, 2010 at 2:41 pm
Actual (not estimated) execution plan. saved as .sqlplan and attached to your post would help a lot.
March 1, 2010 at 4:52 pm
I am adding 2 plans as attachements both are completely identical but one is lsow and the other is fast.
query is a simple query and index are also rebuilt on both database but still slow.
March 1, 2010 at 5:11 pm
The two query plans are not identical.
The slow plan is actually processing 2.509.700.160 (2 and a half billion!) rows from tempdb.dbo.#revdates (estimated: 454080). See the thick arrow on the left most Nested Loop part.
Would you please verify, how many rows are actually in that table?
March 1, 2010 at 5:16 pm
as i said bot hare idential except the slow qry is a smaller database comapred to fast_qry and here is hte count of #revdates table respectively.
slow_qry = 454080
fast_qry = 4274848
March 1, 2010 at 6:10 pm
Besides the number of rows, I am seeing that the indexes are not the same.
Your server that is slow is missing an index. 34% of your cost there is against a table scan of #revdates.
Try an index on empnumber, effectivedate and expirationdate.
Beyond that, it is extremely beneficial to post your actual queries being run on one v. the other. You said the queries are similar - which means they are not the same. The databases are also not quite the same due to the missing indexes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2010 at 6:31 pm
yes you are right, i see an index misssing at this point am not sure how that index is missing i may have to research but that is from the fast qry but not in the slow qry its really strange.
March 2, 2010 at 1:08 am
doube post
March 2, 2010 at 1:10 am
are the databases on the same server/hardware setup? second as Lutz said your slow queries temp table is like 500x larger than in your fast query. And are you sure the database design is the same if so did the column name change from empcode to servcdate in the vwrx table?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply