SQL Server Performance Forum – Threads Archive

Problem with SQL query

I am trying to tune a query that is returning activities of the day from a calendar.
The query executes rather slowly on the SQL-server and I have tried to create an index to speed it up. Unfortunately it does not seem to be enough to create the index on only the "time" column. If I use "INDEX 1" and then run "SQL 1", the index is being used by the SQL-server and the query is executing very fast!
But when I try to create "INDEX 2" I receive an error message: "operation failed. The index entry of length 942 bytes for the index exceeds the maximum length of 900 bytes." Is it possible to rebuild "SQL 2" in any other way or is it possible to create an index that "SQL 2" will use when executing? /Kalle ———- calendar_data ——— id int (primary key)
id_page int
time datetime
subject varchar(160)
message varchar(2500) ————– INDEX 1 ————– time
id_page —————- SQL 1 ————– DECLARE @StartTime datetime, @EndTime datetime SELECT @StartTime = CAST((right(’00’ + Cast(Year(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Month(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Day(GetDate()) AS varchar), 2)) AS datetime)
SELECT @EndTime = DATEADD(ss, -1, DATEADD(dd, 1, @StartTime)) SELECT id_page, YEAR(time), MONTH(time), DAY(time),
FROM calendar_data
WHERE time > @StartTime AND time < @EndTime
ORDER BY time ASC ————– INDEX 2 ————– time
id_page
subject
message —————- SQL 2 ————– DECLARE @StartTime datetime, @EndTime datetime SELECT @StartTime = CAST((right(’00’ + Cast(Year(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Month(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Day(GetDate()) AS varchar), 2)) AS datetime)
SELECT @EndTime = DATEADD(ss, -1, DATEADD(dd, 1, @StartTime)) SELECT id_page, subject, message, YEAR(time), MONTH(time), DAY(time),
FROM calendar_data
WHERE time > @StartTime AND time < @EndTime
ORDER BY time ASC

About Index 2, you have to install last service pack. Check: http://support.microsoft.com/default.aspx?scid=kb;en-us;280744 Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

After installation of SP3 I now receive a message from Enterprise Manager when trying to create INDEX 2: "Total size of an index or primary key cannot exceed 900 bytes". Any ideas why its not working?

The error message is pretty much self-describing. You *cannot* create such a wide index in SQL Server 2000. No way around. But btw, your statement <pre id="code"> SELECT CAST((right(’00’ + Cast(Year(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Month(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Day(GetDate()) AS varchar), 2)) AS datetime) </pre id="code"> is inefficient and produces, at least on my system wrong results. <pre id="code"> SELECT CAST((right(’00’ + Cast(Year(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Month(GetDate()) AS varchar), 2) + ‘-‘ + right(’00’ + Cast(Day(GetDate()) AS varchar), 2)) AS datetime) SELECT DATEADD(d,0,DATEDIFF(d,0,GETDATE())) —————————————————— 2001-05-04 00:00:00.000 (1 row(s) affected) —————————————————— 2005-04-01 00:00:00.000 (1 row(s) affected) </pre id="code"> You see the difference? It is better to use a safe date format like described in BOL in CONVERT. See style 112 or 126. To set the time to midnight there are several methods available. You might want to choose one from the following: <pre id="code"> SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0) SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) + 0x00000000 AS DATETIME) SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) AS INT) AS DATETIME) SELECT CONVERT(DATETIME,CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,GETDATE(),112)) SELECT CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,GETDATE(),112) SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME) SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) </pre id="code"> Personally I prefer the first one. Gives probably the best performance. – Frank http://www.insidesql.de</a>

Thank you Frank! I have now rewritten the query but I still have the same problem.
Why isn’t it enough to create an index on only the "time" column? If I create an index on "time och "id_page" the SQL-server uses the index when running SQL 2. But why does the SQL-server need "id_page" in the index? I need to run SQL 1 that also return "subject" and "message" but creating an index on "time", "id_page", "subject", "message" seems a little bit strange, or have I misunderstood how indexes works? —– DECLARE @StartTime datetime, @EndTime datetime SELECT @StartTime = DATEADD(d,DATEDIFF(d,0,getdate()),0)
SELECT @EndTime = DATEADD(dd, 1, @StartTime) — SQL 1 SELECT id_page, subject, message
FROM calender_data
WHERE from_time >= @StartTime AND from_time < @EndTime
ORDER BY from_time ASC — SQL 2 SELECT id_page
FROM calender_data
WHERE from_time >= @StartTime AND from_time < @EndTime
ORDER BY from_time ASC

is your primary key on id clustered?
does it need to be?
do you frequently run queries like:
SELECT * FROM calendar_data WHERE id BETWEEN @1 AND @2 if not, then consider making the primary key non-clustered,
then make a clustered index on time
if time is unique, make it a unique clustered index
if time, id_page combined it unique, make that the clustered index
otherwise something like time, id

Thank you Joe! You solved the problem! I created a clustered unique index on "time" and "id" and now every query uses the index!
I do not runt queries that uses "id" frequently so this solution is perfect for me!

]]>