473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Query Execution Speed

Hi there - i'm hoping someone can help me!

I'm having a problem with a live database that i'm running on MSDE - It
seems to have slowed down quite considerably from the test environment
(even when all the data is the same). The is notably different on one
particular query that takes 1 sec on the test machine and almost 1 min
on the live machine

The total number of user connections on the live machine is normally 4
or so (found out through the Performance monitor). So I can't see that
it's MSDE's performance throttler...

Has anybody got any ideas on things i can check for??
Many thanks

James

Jul 23 '05 #1
8 2013
I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!

Jul 23 '05 #2
I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!

Help gratefully accepted!!

Jul 23 '05 #3
Do the execution plans look different on the two servers? We had
similar problems and it turned out that the one plan used parallelism
and the other didn't.

Jul 23 '05 #4
It seems that the execution plans are different!!! I'm a bit new at
this kind of configuration with SQL Server... what do I need to change
to make the execution plans the same on both machines?

Jul 23 '05 #5
This is not unlike how two people perform the same task and get the
same result, but the process of performing it differs. In this case, I
assume the servers are not exactly the same, the physical distribution
of data on disk could be different, the load on the server could be
different, etc.

In general terms, I would try the following:

1. Update statistics on both servers, then compare execution plans
again.
2. Add query hints on the slower plan to get the desired result if the
above does not change things.

How are the plans different?

Jul 23 '05 #6
Sorry, I didnt' explain myself properly and I guess used the wrong
terminology somewhere along the line!!

Every evening, a copy of the database is made to another database on
the same instance of SQL Server. When I execute the SP on this
'snapshot' database it only takes a few seconds, but when I execute the
original it takes up to a minute or so.

So - the database is on the same SQL Server and has exactly (albeit to
a few hours) the same data as the live database, yet the execution
plans are different and the speed is dramatically different!!

Jul 23 '05 #7
Here are the two execution plans (I hope this is the right format that
you can understand...)

Query running on live data (slow):

|--Sort(ORDER BY:([Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE:([Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Filter(WHERE:(If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@MinDaysCover]<>NULL) then Convert([@MinDaysCover]) else If
([Expr1013]=0) then 0 else (
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([StockLevel].[StockLine_ID]))
|--Bookmark Lookup(BOOKMARK:([Bmk1011]),
OBJECT:([foodcontrolSQL].[dbo].[Supplier]))
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([Product].[Supplier_ID]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([StockLine].[StockLine_ID]) WITH PREFETCH)
| |
|--Filter(WHERE:([StockLine].[StockLineStatus_ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@ExcludeFutureDelist])=1) then 0 else NULL<>NULL) then If
(Convert([@ExcludeFutureDelist])=1) then 0 el
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1007]),
OBJECT:([foodcontrolSQL].[dbo].[StockLine]))
| | | |--Nested
Loops(Inner Join, OUTER REFERENCES:([Product].[Product_ID]) WITH
PREFETCH)
| | | |--Table
Scan(OBJECT:([foodcontrolSQL].[dbo].[Product]),
WHERE:([Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@SupplierFilter])=1) then [@Supplier_ID] else NULL<>NULL)
t
| | | |--Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[StockLine].[Product_ID]),
SEEK:([StockLine].[Product_ID]=[Product].[Product_ID]) ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[StockLevel].[PK__StockLevelBackup__5E74FADA]),
SEEK:([StockLevel].[StockLocation_ID]=1 AND
[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]), WHERE:([S
| |--Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[Supplier].[Supplier_ID]),
SEEK:([Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)
|--Compute
Scalar(DEFINE:([Expr1013]=Convert([Expr1004])/Convert([@NumDays])))
|--Compute Scalar(DEFINE:([Expr1004]=If
([Expr1032]=0) then NULL else [Expr1033]))
|--Stream
Aggregate(DEFINE:([Expr1032]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1033]=SUM([StockTransaction].[StockAdjustmentQuantity])))

|--Filter(WHERE:((([StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]
AND [StockTransaction].[ExcludeROS]=If ([@ExcludeROS]<>NULL) then
[@ExcludeROS] else [StockTransaction].[ExcludeROS]) AND [StockTrans
|--Bookmark
Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([foodcontrolSQL].[dbo].[StockTransaction]))
|--Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[StockTransaction].[StockTransaction11]),
SEEK:([StockTransaction].[TransactionDate] >= [@StartDate] AND
[StockTransaction].[TransactionDate] <= [@EndDate]) OR

Query running on snapshot data (fast):

|--Sort(ORDER BY:([Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE:([Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Bookmark Lookup(BOOKMARK:([Bmk1011]),
OBJECT:([foodcontrolSnapshot].[dbo].[Supplier]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([Product].[Supplier_ID]))
|--Hash Match(Inner Join,
HASH:([StockLine].[Product_ID])=([Product].[Product_ID]),
RESIDUAL:([Product].[Product_ID]=[StockLine].[Product_ID]))
| |--Merge Join(Inner Join,
MERGE:([StockLine].[StockLine_ID])=([StockLevel].[StockLine_ID]),
RESIDUAL:([StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]))
| | |--Sort(ORDER
BY:([StockLine].[StockLine_ID] ASC))
| | | |--Table
Scan(OBJECT:([foodcontrolSnapshot].[dbo].[StockLine]),
WHERE:([StockLine].[StockLineStatus_ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@ExcludeFutureDelist])=1) then 0 else NULL<>NULL)
| | |--Filter(WHERE:(If ([Expr1013]=0)
then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@MinDaysCover]<>NULL) then Convert([@MinDaysCover]) else If ([Expr10
| | |--Merge Join(Left Outer
Join,
MERGE:([StockLevel].[StockLine_ID])=([StockTransaction].[StockLine_ID]),
RESIDUAL:([StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]))
| | |--Clustered Index
Seek(OBJECT:([foodcontrolSnapshot].[dbo].[StockLevel].[PK__StockLevel__6DA725A5]),
SEEK:([StockLevel].[StockLocation_ID]=1),
WHERE:([StockLevel].[CurrentStockQuantity]-[StockLevel].[Committ
| | |--Compute
Scalar(DEFINE:([Expr1013]=Convert([Expr1004])/Convert([@NumDays])))
| | |--Compute
Scalar(DEFINE:([Expr1004]=If ([Expr1035]=0) then NULL else [Expr1036]))
| | |--Stream
Aggregate(GROUP BY:([StockTransaction].[StockLine_ID])
DEFINE:([Expr1035]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1036]=SUM([StockTransaction].[StockAdjustmentQuantity
| |
|--Sort(ORDER BY:([StockTransaction].[StockLine_ID] ASC))
| |
|--Clustered Index
Scan(OBJECT:([foodcontrolSnapshot].[dbo].[StockTransaction].[PK_StockTransaction]),
WHERE:(((([StockTransaction].[StockLocation_ID]=1 AND
[StockTransaction].[StockTransac
| |--Table
Scan(OBJECT:([foodcontrolSnapshot].[dbo].[Product]),
WHERE:([Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@SupplierFilter])=1) then [@Supplier_ID] else NULL<>NULL)
then If (Convert([@Su
|--Index
Seek(OBJECT:([foodcontrolSnapshot].[dbo].[Supplier].[aaaaaSupplier_PK]),
SEEK:([Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)

Jul 23 '05 #8
Just in case anybody is reading this topic and wants to know how i
solved the problem, I've run the command

UPDATE STATISTICS tablename

for each table that was dependent on the query and it's made the
queries run nice and fast again!!

Jul 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Muharram Mansoorizadeh | last post by:
Hi there, I've a table with 18 millions of recordes shaped like this : Code nvarchar(80) , State int , school int , class int , Term nvarchar(80) The following query takes too long to run ( more...
3
by: robboll | last post by:
Whenever I query my database using Enterprise Manager, the response time is about a second. When I access the same data via a Cold Fusion webpage, it takes about 15 seconds (or more) to resolve. ...
15
by: Jean | last post by:
Hello, I have the following query that I set up as a test, and it runs fine: SELECT STATUSHISTORIE.* FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =...
3
by: serge | last post by:
How do I determine which method I should use if I want to optimize the performance of a database. I took Northwind's database to run my example. My query is I want to retrieve the Employees'...
5
by: darnnews | last post by:
Hi, I have been creating a database to keep track of press clippings, but I have hit a couple stumbling blocks. Any help is much appreciate. 1) Seeing if my query is done I have the...
30
by: Paul H | last post by:
I seem to end up with loads of append and update queries just because it's quick and easy to build queries or make a new ones based on an existing query. But I end up with loads of queries with...
5
by: - | last post by:
I have only 1m records in my database running on a laptop of speed 1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk. I use 'LIMIT x,10' for the query to utilise record paging. When the...
15
by: wizofaus | last post by:
I have a chunk of code which is essentially IDbCommand cmd = db.CreateCommand(); cmd.CommandText = "SELECT X, Y, Count(*) FROM Foo WHERE Z = 1 GROUP BY X, Y"; using (IDataReader reader =...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
1
by: Don Li | last post by:
Hi, Env: MS SQL Server 2000 DB Info (sorry no DDL nor sample data): tblA has 147249 rows -- clustered index on pk (one key of datatype(int)) and has two clumns, both are being used in joins;...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.