Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.tools > #2

Re: SQL Performance Issue Using Variable Dates vs. Literal Dates

Show all headers | View raw

On Sunday, January 25, 2004 at 2:16:35 AM UTC+5:30, Christopher D. Wiederspan wrote:
> I thought I knew enough about SQL Server 2000 that I understood my queries,
> but I've recently come across a situation that's very perplexing to me. The
> situation is that I've written a fairly straight forward query that runs
> very quickly one way, but very slowly when I make what I thougth was a minor
> change. The fast version is this:
> 
> SELECT
>  VS.PartnerID,
>  VS.PartnerCode,
>  COUNT(DISTINCT VS.VisitorSessionID)  'Clicks',
>  COUNT(A.ApplicationID)    'Applications',
>  SUM(A.Revenue)      'Revenue'
> 
> FROM VisitorSessions AS VS (NOLOCK)
> 
> LEFT JOIN VisitorApplications AS VA (NOLOCK)
> ON VS.VisitorSessionID = VA.VisitorSessionID
> 
> LEFT JOIN PayableApplications AS A (NOLOCK)
> ON VA.ApplicationID = A.ApplicationID
> 
> WHERE VS.StartDate >= '1/1/2004'         -- *** NOTICE THIS
>   AND VS.StartDate < '1/2/2004'              -- *** NOTICE THIS
> 
> GROUP BY VS.PartnerID, VS.PartnerCode
> 
> 
> My problems began when I though I'd make my life simpler by replacing the
> "hard-coded" dates in the WHERE clause with variable dates, like this:
> 
> 
> DECLARE @StartDate DATETIME
> DECLARE @EndDate DATETIME
> 
> SET @StartDate = '1/1/2004'
> SET @EndDate = '1/2/2004'
> 
> SELECT
>  VS.PartnerID,
>  VS.PartnerCode,
>  COUNT(DISTINCT VS.VisitorSessionID)  'Clicks',
>  COUNT(A.ApplicationID)    'Applications',
>  SUM(A.Revenue)      'Revenue'
> 
> FROM VisitorSessions AS VS (NOLOCK)
> 
> LEFT JOIN VisitorApplications AS VA (NOLOCK)
> ON VS.VisitorSessionID = VA.VisitorSessionID
> 
> LEFT JOIN PayableApplications AS A (NOLOCK)
> ON VA.ApplicationID = A.ApplicationID
> 
> WHERE VS.StartDate >= @StartDate
>   AND VS.StartDate < @EndDate
> 
> GROUP BY VS.PartnerID, VS.PartnerCode
> 
> 
> All of the sudden, my query that previously took 1 or 2 seconds was now
> taking 45 seconds. My tables are nothing out of the ordinary, and contain a
> lot, but not unreasonable amounts of data. When using a combination of Query
> Analyzer and SQL Profiler, I can definitely see that SQL Server is executing
> the queries much differently, but I don't understand why...
> 
> I've since gone back to some of the 10's (if not 100's) of queries and
> Stored Procedures within our database and tested to see if they suffer from
> the same problem - THEY DO! With that said, you can understand that I feel
> like I may be loosing a ton of performance by doing things this way.
> 
> Please let me know if you have any insight into this issue - I can defnitely
> use the help!
> 
> Thanks,
> Chris

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id
-------------------------------

Do this
DECLARE @sql varchar(max)
SET @sql='SELECT * FROM View1 WHERE ID ='+CAST(@id as varchar)
EXEC (@sql)

Solves your problem

Back to microsoft.public.sqlserver.tools | Previous | Next | Find similar

Thread

Re: SQL Performance Issue Using Variable Dates vs. Literal Dates neeraj.kalyan@nimbusitsolutions.com - 2018-04-25 05:41 -0700

csiph-web