| Author |
 Topic  |
|
timlisten
Starting Member
USA
26 Posts |
 Posted - 06/08/2012 : 17:01:37
|
I have a table that has a DATETIME variable.
I try to execute
select * from Table where date = '2012-06-07'
the query takes 0 seconds to run which is good. However, when I run it with a variable, it took 7 seconds. why is that? They return the same data.
DECLARE @Date DATETIME
SELECT @Date = MAX(Date) FROM Table
select * from Table where date = @Date
The @Date is 2012-06-07 in this case.
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 06/08/2012 : 17:15:56
|
| Ignoring that tables don't have variables (I assume you mean a DATETIME column). Does the SELECT take 7 seconds or does the BATCH take 7 seconds; The batch being the SELECT MAX() and the SELECT *? |
 |
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/08/2012 : 17:41:27
|
Yah, the column is a DateTime. The batch takes 7 seconds, but I just tried with the following without using MAX, it still takes 7 minutes.
All I'm doing is declare a variable and set it to a constant.
DECLARE @Date DATETIME
SET @Date ='2012-06-07'
select * from Table where date = @Date |
Edited by - timlisten on 06/08/2012 17:42:02 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47154 Posts |
Posted - 06/09/2012 : 02:12:15
|
what are indexes present in yourtable?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/09/2012 : 19:31:39
|
| The DateTime is a column with index. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47154 Posts |
Posted - 06/09/2012 : 20:08:21
|
is it getting used? check execution plan
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/09/2012 : 20:15:30
|
I did a Google search and other people having similar problems, but I'm not able to find the solution to this. I found something like this
-----
"When SQL starts to optimize the query plan for the query with the variable it will match the available index against the column. In this case there was an index so SQL figured it would just scan the index looking for the value. When SQL made the plan for the query with the column and a literal value it could look at the statistics and the value to decide if it should scan the index or if a seek would be correct.
Using the optimize hint and a value tells SQL that “this is the value which will be used most of the time so optimize for this value” and a plan is stored as if this literal value was used. Using the optimize hint and the sub-hint of UNKNOWN tells SQL you do not know what the value will be, so SQL looks at the statistics for the column and decides what, seek or scan, will be best and makes the plan accordingly."
--------
Some people suggested to use OPTIMIZE FOR (@Date = 1) and it does run faster, but I got syntax error when trying to apply it to multiple where clauses. Also, I read there are performance disadvantage using OPTIMIZE FOR (@ID = 1) in some cases. |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 06/10/2012 : 02:05:14
|
Clear down the cache with DBCC FREEPROCCACHE and try again , do you still get 7 seconds?
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/10/2012 : 09:56:26
|
| How to clear the cache? I'm on a share hosting, so I have limited rights on the sql server. |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 06/11/2012 : 10:37:33
|
use these 2 commands on your SQL Server instance - assuming you have the permissions
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/11/2012 : 14:54:39
|
| I tried it and unfortunately it says that I don't have permission to do that. |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 06/12/2012 : 00:43:37
|
Could you request - someone gives you elevated rights - or ask someone else to issue command?
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/14/2012 : 15:29:50
|
I noticed something weird about my sql queries. I'm on a share hosting with Arvixe.
One query takes 11 seconds to run during day time, but at night time, sometimes it takes over 5-10 minutes to run.
I contact the support and they told me nothing is wrong on the server. The query used to work fine all year, and the problem occur recently.
Do you think my sql queries will be more stable and always run around 11 seconds if I upgrade to a VPS?
For example, a simple query like
SELECT * from Table where Date >= '2012-06-12' takes 1-2 seconds to run during day time, but sometimes it takes over 30 seconds to run. The date column is index and the statement returns about 8,000 records from a table of about 1 million records. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47154 Posts |
Posted - 06/14/2012 : 15:40:43
|
have a look at profiler to see if there are any other intensive processes running at the time?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/14/2012 : 16:19:15
|
| I have some jobs running, but not intensive. What I'm worry about is the other people on the same share hosting that are running intensive process on their sql and that's slowing me down. Will that have any major impact on my queries? I'm thinking to upgrade to VPS, but then their support told me the performance should be better, but they don't make any guarantee. My queries were running fine for like 2-3 years and there is no increase on the website traffic, so I'm not sure if I should do the upgrade. |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 06/15/2012 : 01:42:21
|
Have you run an UPDATE Statistics on the relevant indexes? If they are not up to date this could slow the query
It is possible - the query in the evening - may be during a backup window . If it's shared hosting , and you're on a VM and shared, speak to your hosting company. Ask them about:
a) memory commitment
b)Other services - such as Virus Scanning going on at the same. Not only on your VM , but also other VMs.
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/15/2012 : 02:10:52
|
MAX have to scan the entire index. Try this instead.
DECLARE @Date DATETIME
SELECT TOP(1) @Date = [Date] FROM Table ORDER BY [Date] DESC
SELECT * FROM Table WHERE [Date] = @Date
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/15/2012 : 02:11:37
|
Or why not this directly?
SELECT TOP(1) WITH TIES * FROM dbo.Table ORDER BY [Date] DESC
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/15/2012 : 23:35:55
|
quote:
Originally posted by jackv
Have you run an UPDATE Statistics on the relevant indexes? If they are not up to date this could slow the query
It is possible - the query in the evening - may be during a backup window . If it's shared hosting , and you're on a VM and shared, speak to your hosting company. Ask them about:
a) memory commitment
b)Other services - such as Virus Scanning going on at the same. Not only on your VM , but also other VMs.
Jack Vamvas
--------------------
http://www.sqlserver-dba.com
I actually have some background jobs running in the evening, a lot of sql queries doing calculations and stuff. However, I'm doing that for the past 2 years and there weren't any major problems (I added a few more background jobs along the way). I'm using their personal package which allow 250MB. Their business package allow 500MB. The problem is I'm not sure how much resources I'm using, so I'm not sure if upgrading to VPS will help. Will the queries run a lot faster on a VPS than a sharing hosting? |
Edited by - timlisten on 06/15/2012 23:37:02 |
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/15/2012 : 23:39:39
|
quote:
Originally posted by SwePeso
Or why not this directly?
SELECT TOP(1) WITH TIES * FROM dbo.Table ORDER BY [Date] DESC
N 56°04'39.26"
E 12°55'05.63"
Thanks, I used to do this, and I thought MAX is shorter so I switched to using MAX. I didn't know TOP 1 actually performs better. In the future, I will use TOP 1. |
|
|
|
Motumoyo
Starting Member
USA
3 Posts |
Posted - 06/29/2012 : 23:24:56
|
See if there are any other run-time-intensive processes?
unspammed |
Edited by - Motumoyo on 07/01/2012 21:49:55 |
|
|
|
Topic |
|
Question about performance
