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)
 Question about performance

Author  Topic 

timlisten
Starting Member

26 Posts
Posted - 2012-06-08 : 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
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-06-08 : 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 *?
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-08 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-06-09 : 02:12:15
what are indexes present in yourtable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-09 : 19:31:39
The DateTime is a column with index.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-06-09 : 20:08:21
is it getting used? check execution plan

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-09 : 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.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts
Posted - 2012-06-10 : 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
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-10 : 09:56:26
How to clear the cache? I'm on a share hosting, so I have limited rights on the sql server.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts
Posted - 2012-06-11 : 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
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-11 : 14:54:39
I tried it and unfortunately it says that I don't have permission to do that.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts
Posted - 2012-06-12 : 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
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-14 : 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-06-14 : 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/
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-14 : 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.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts
Posted - 2012-06-15 : 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-06-15 : 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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-06-15 : 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"
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-15 : 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?
Go to Top of Page

timlisten
Starting Member

26 Posts
Posted - 2012-06-15 : 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.
Go to Top of Page

Motumoyo
Starting Member

3 Posts
Posted - 2012-06-29 : 23:24:56
See if there are any other run-time-intensive processes?
unspammed
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site