Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdbOn Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.
4ax.com:

> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Roy Harvey <roy_harvey@.snet.net> wrote in
> news:k4uf935qq5jqltamrffo96mgsemsnojorh@.
4ax.com:
>
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdbOn Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:
>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:
> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:
>Roy Harvey <roy_harvey@.snet.net> wrote in
>news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:
>> If the index were clustered on the date then it certainly will help.
>> But I would not rush to cluster on the date, the choice of a proper
>> clustered index is not that simple.
>> If the date ranges are very narrow compared to the overall table then
>> a non-clustered index on the date may very well be chosen by the
>> optimizer and save time. The optimizer is going to estimate the
>> number of pages it will have to read in random order to use the index,
>> and compare that to the number of pages it will have to read to scan
>> the entire table. The smaller number wins.
>> Without more information on the table, data and query I don't know
>> what else to say.
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdb
On Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT
|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:

> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb
|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Roy Harvey <roy_harvey@.snet.net> wrote in
>news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com :
>
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

Help needed please on Stored procedure logic

Hi all,
I have the following sproc which is not giving me the results I would
anticipate it should. Basically what the logic needed is that there
are a load of tickets for an event, I need a sproc that holds (by
setting a database flag) any number of tickets the users want, this is
a true multi user environment. This sproc holds the required mount and
if there are not enough tickets then it reports that but in a 2 user
test, say for 4 tickets each, and where there are loads of tickets,
the first user gets 4, the other gets a message saying there are not
enough. Is it to due with viariable sharing or soemthing
The idea was to loop round the database for available tickets, update
the flag and once done commit the transaction, otherwise roll it back
Any thoughts'
Cehers
Shaun
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 0
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < (@.NumberReqd - @.CurrentTicket)
GOTO None_Left
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId
ORDER BY [Event Tickets].[Ticket Ref])
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
CONTINUE
END
COMMIT TRAN BIP
SET @.Resp = 0
RETURN
None_Left:
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
GOTicket reservations are extremely complicated. What happens when people
want two seats in one row and three seats in the row behind them? How about
three rows of four seats? As you can see, simply assigning seats by the
order that they appear within your table might not be enough. Anyway, here
is one method that assigns tickets based on your system.
It is probably best to show you an example, so I created a table, some
sample data, and a stored procedure.
/*
I have no idea what datatype TicketStatus is. At one point you use '0'
within a query and later on you use 1 (no quotes).
*/
USE tempdb
GO
CREATE TABLE #EventTickets (TicketRef int, SeatSection varchar(10), SeatRow
varchar(10), SeatNo int,
TicketStatus int, EventID int, ReserveUserID int,
ReserveDate datetime)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (1, 'Floor', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (2, 'Floor', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (3, 'Floor', '1', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (4, 'Floor', '1', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (5, 'Floor', '1', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (6, 'Floor', '2', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (7, 'Floor', '2', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (8, 'Floor', '2', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (9, 'Floor', '2', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (10, 'Floor', '2', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (11, 'Floor', '2', 6, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (12, 'Floor', '2', 7, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (13, '100', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (14, '100', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (15, '100', '1', 3, 0, 10)
GO
CREATE PROC FindAndReserve
@.UserId int,
@.EventId int,
@.NumberReqd int,
@.Resp int OUTPUT
AS
SET NOCOUNT ON
--select * from #EventTickets
IF EXISTS (SELECT * FROM #EventTickets A WHERE A.TicketStatus = 0)
BEGIN
IF (SELECT COUNT(*) FROM #EventTickets A
WHERE A.TicketStatus = 0
AND A.EventID = @.EventId) < @.NumberReqd
BEGIN
PRINT 'not enough tickets left' /*just for a visual; remove before use in
production*/
SET @.Resp = 1
RETURN (0)
END
DECLARE @.TicketsToReserve TABLE (TicketRef int)
BEGIN TRANSACTION
SET ROWCOUNT @.NumberReqd
/*get a list of tickets that we are going to reserve*/
INSERT INTO @.TicketsToReserve
SELECT TicketRef FROM #EventTickets WHERE TicketStatus = 0 ORDER BY
TicketRef
UPDATE #EventTickets
SET TicketStatus = 1,
ReserveUserID = @.UserId,
ReserveDate = GetDate()
FROM #EventTickets A JOIN @.TicketsToReserve B ON A.TicketRef = B.TicketRef
PRINT 'Reservation complete' /*just for a visual; remove before use in
production*/
SET @.Resp = 0
COMMIT TRANSACTION
RETURN (0)
END
PRINT 'Commpletely sold out' /*just for a visual; remove before use in
production*/
SET @.Resp = 3
GO
----
---
----
---
select * from #EventTickets
go
/*not enough tickets*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 1000,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
/*start the reservations!*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 526,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 87,
@.EventId = 10,
@.NumberReqd = 3,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 800,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 5,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 1,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
/*RESET*/
-- UPDATE #EventTickets SET TicketStatus = 0, ReserveUserID = NULL,
ReserveDate = NULL
-- drop proc FindAndReserve
Keith
"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411160632.1c24a330@.posting.google.com...
> Hi all,
> I have the following sproc which is not giving me the results I would
> anticipate it should. Basically what the logic needed is that there
> are a load of tickets for an event, I need a sproc that holds (by
> setting a database flag) any number of tickets the users want, this is
> a true multi user environment. This sproc holds the required mount and
> if there are not enough tickets then it reports that but in a 2 user
> test, say for 4 tickets each, and where there are loads of tickets,
> the first user gets 4, the other gets a message saying there are not
> enough. Is it to due with viariable sharing or soemthing
> The idea was to loop round the database for available tickets, update
> the flag and once done commit the transaction, otherwise roll it back
> Any thoughts'
> Cehers
> Shaun
>
> CREATE PROCEDURE BIP_Tickets @.UserId Integer,
> @.EventId Integer,
> @.NumberReqd as Integer,
> @.Resp Integer OUTPUT AS
> SET NOCOUNT ON
> Declare @.CurrentTicket Integer
> Declare @.TicketRef Integer
> SET @.CurrentTicket = 0
>
> BEGIN TRAN BIP
> WHILE @.CurrentTicket < @.NumberReqd
> BEGIN
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < (@.NumberReqd - @.CurrentTicket)
> GOTO None_Left
> SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
> FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId
> ORDER BY [Event Tickets].[Ticket Ref])
> UPDATE [Event Tickets]
> SET [Ticket status] = 1,
> [User Id] = @.UserId,
> [Status Updated] = GetDate()
> WHERE [Ticket Ref] = @.TicketRef
> SET @.CurrentTicket = @.CurrentTicket + 1
> CONTINUE
> END
> COMMIT TRAN BIP
> SET @.Resp = 0
> RETURN
> None_Left:
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> GO

Help needed please on Stored procedure logic

Hi all,
I have the following sproc which is not giving me the results I would
anticipate it should. Basically what the logic needed is that there
are a load of tickets for an event, I need a sproc that holds (by
setting a database flag) any number of tickets the users want, this is
a true multi user environment. This sproc holds the required mount and
if there are not enough tickets then it reports that but in a 2 user
test, say for 4 tickets each, and where there are loads of tickets,
the first user gets 4, the other gets a message saying there are not
enough. Is it to due with viariable sharing or soemthing
The idea was to loop round the database for available tickets, update
the flag and once done commit the transaction, otherwise roll it back
Any thoughts'
Cehers
Shaun
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 0
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < (@.NumberReqd - @.CurrentTicket)
GOTO None_Left
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId
ORDER BY [Event Tickets].[Ticket Ref])
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
CONTINUE
END
COMMIT TRAN BIP
SET @.Resp = 0
RETURN
None_Left:
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
GOTicket reservations are extremely complicated. What happens when people
want two seats in one row and three seats in the row behind them? How about
three rows of four seats? As you can see, simply assigning seats by the
order that they appear within your table might not be enough. Anyway, here
is one method that assigns tickets based on your system.
It is probably best to show you an example, so I created a table, some
sample data, and a stored procedure.
/*
I have no idea what datatype TicketStatus is. At one point you use '0'
within a query and later on you use 1 (no quotes).
*/
USE tempdb
GO
CREATE TABLE #EventTickets (TicketRef int, SeatSection varchar(10), SeatRow
varchar(10), SeatNo int,
TicketStatus int, EventID int, ReserveUserID int,
ReserveDate datetime)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (1, 'Floor', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (2, 'Floor', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (3, 'Floor', '1', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (4, 'Floor', '1', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (5, 'Floor', '1', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (6, 'Floor', '2', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (7, 'Floor', '2', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (8, 'Floor', '2', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (9, 'Floor', '2', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (10, 'Floor', '2', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (11, 'Floor', '2', 6, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (12, 'Floor', '2', 7, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (13, '100', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (14, '100', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (15, '100', '1', 3, 0, 10)
GO
CREATE PROC FindAndReserve
@.UserId int,
@.EventId int,
@.NumberReqd int,
@.Resp int OUTPUT
AS
SET NOCOUNT ON
--select * from #EventTickets
IF EXISTS (SELECT * FROM #EventTickets A WHERE A.TicketStatus = 0)
BEGIN
IF (SELECT COUNT(*) FROM #EventTickets A
WHERE A.TicketStatus = 0
AND A.EventID = @.EventId) < @.NumberReqd
BEGIN
PRINT 'not enough tickets left' /*just for a visual; remove before use in
production*/
SET @.Resp = 1
RETURN (0)
END
DECLARE @.TicketsToReserve TABLE (TicketRef int)
BEGIN TRANSACTION
SET ROWCOUNT @.NumberReqd
/*get a list of tickets that we are going to reserve*/
INSERT INTO @.TicketsToReserve
SELECT TicketRef FROM #EventTickets WHERE TicketStatus = 0 ORDER BY
TicketRef
UPDATE #EventTickets
SET TicketStatus = 1,
ReserveUserID = @.UserId,
ReserveDate = GetDate()
FROM #EventTickets A JOIN @.TicketsToReserve B ON A.TicketRef = B.TicketRef
PRINT 'Reservation complete' /*just for a visual; remove before use in
production*/
SET @.Resp = 0
COMMIT TRANSACTION
RETURN (0)
END
PRINT 'Commpletely sold out' /*just for a visual; remove before use in
production*/
SET @.Resp = 3
GO
----
---
----
---
select * from #EventTickets
go
/*not enough tickets*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 1000,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
/*start the reservations!*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 526,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 87,
@.EventId = 10,
@.NumberReqd = 3,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 800,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 5,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 1,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
/*RESET*/
-- UPDATE #EventTickets SET TicketStatus = 0, ReserveUserID = NULL,
ReserveDate = NULL
-- drop proc FindAndReserve
--
Keith
"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411160632.1c24a330@.posting.google.com...
> Hi all,
> I have the following sproc which is not giving me the results I would
> anticipate it should. Basically what the logic needed is that there
> are a load of tickets for an event, I need a sproc that holds (by
> setting a database flag) any number of tickets the users want, this is
> a true multi user environment. This sproc holds the required mount and
> if there are not enough tickets then it reports that but in a 2 user
> test, say for 4 tickets each, and where there are loads of tickets,
> the first user gets 4, the other gets a message saying there are not
> enough. Is it to due with viariable sharing or soemthing
> The idea was to loop round the database for available tickets, update
> the flag and once done commit the transaction, otherwise roll it back
> Any thoughts'
> Cehers
> Shaun
>
> CREATE PROCEDURE BIP_Tickets @.UserId Integer,
> @.EventId Integer,
> @.NumberReqd as Integer,
> @.Resp Integer OUTPUT AS
> SET NOCOUNT ON
> Declare @.CurrentTicket Integer
> Declare @.TicketRef Integer
> SET @.CurrentTicket = 0
>
> BEGIN TRAN BIP
> WHILE @.CurrentTicket < @.NumberReqd
> BEGIN
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < (@.NumberReqd - @.CurrentTicket)
> GOTO None_Left
> SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
> FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId
> ORDER BY [Event Tickets].[Ticket Ref])
> UPDATE [Event Tickets]
> SET [Ticket status] = 1,
> [User Id] = @.UserId,
> [Status Updated] = GetDate()
> WHERE [Ticket Ref] = @.TicketRef
> SET @.CurrentTicket = @.CurrentTicket + 1
> CONTINUE
> END
> COMMIT TRAN BIP
> SET @.Resp = 0
> RETURN
> None_Left:
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> GO

Help needed please on Stored procedure logic

Hi all,
I have the following sproc which is not giving me the results I would
anticipate it should. Basically what the logic needed is that there
are a load of tickets for an event, I need a sproc that holds (by
setting a database flag) any number of tickets the users want, this is
a true multi user environment. This sproc holds the required mount and
if there are not enough tickets then it reports that but in a 2 user
test, say for 4 tickets each, and where there are loads of tickets,
the first user gets 4, the other gets a message saying there are not
enough. Is it to due with viariable sharing or soemthing
The idea was to loop round the database for available tickets, update
the flag and once done commit the transaction, otherwise roll it back
Any thoughts?
Cehers
Shaun
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 0
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < (@.NumberReqd - @.CurrentTicket)
GOTO None_Left
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId
ORDER BY [Event Tickets].[Ticket Ref])
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
CONTINUE
END
COMMIT TRAN BIP
SET @.Resp = 0
RETURN
None_Left:
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
GO
Ticket reservations are extremely complicated. What happens when people
want two seats in one row and three seats in the row behind them? How about
three rows of four seats? As you can see, simply assigning seats by the
order that they appear within your table might not be enough. Anyway, here
is one method that assigns tickets based on your system.
It is probably best to show you an example, so I created a table, some
sample data, and a stored procedure.
/*
I have no idea what datatype TicketStatus is. At one point you use '0'
within a query and later on you use 1 (no quotes).
*/
USE tempdb
GO
CREATE TABLE #EventTickets (TicketRef int, SeatSection varchar(10), SeatRow
varchar(10), SeatNo int,
TicketStatus int, EventID int, ReserveUserID int,
ReserveDate datetime)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (1, 'Floor', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (2, 'Floor', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (3, 'Floor', '1', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (4, 'Floor', '1', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (5, 'Floor', '1', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (6, 'Floor', '2', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (7, 'Floor', '2', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (8, 'Floor', '2', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (9, 'Floor', '2', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (10, 'Floor', '2', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (11, 'Floor', '2', 6, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (12, 'Floor', '2', 7, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (13, '100', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (14, '100', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (15, '100', '1', 3, 0, 10)
GO
CREATE PROC FindAndReserve
@.UserId int,
@.EventId int,
@.NumberReqd int,
@.Resp int OUTPUT
AS
SET NOCOUNT ON
--select * from #EventTickets
IF EXISTS (SELECT * FROM #EventTickets A WHERE A.TicketStatus = 0)
BEGIN
IF (SELECT COUNT(*) FROM #EventTickets A
WHERE A.TicketStatus = 0
AND A.EventID = @.EventId) < @.NumberReqd
BEGIN
PRINT 'not enough tickets left' /*just for a visual; remove before use in
production*/
SET @.Resp = 1
RETURN (0)
END
DECLARE @.TicketsToReserve TABLE (TicketRef int)
BEGIN TRANSACTION
SET ROWCOUNT @.NumberReqd
/*get a list of tickets that we are going to reserve*/
INSERT INTO @.TicketsToReserve
SELECT TicketRef FROM #EventTickets WHERE TicketStatus = 0 ORDER BY
TicketRef
UPDATE #EventTickets
SET TicketStatus = 1,
ReserveUserID = @.UserId,
ReserveDate = GetDate()
FROM #EventTickets A JOIN @.TicketsToReserve B ON A.TicketRef = B.TicketRef
PRINT 'Reservation complete' /*just for a visual; remove before use in
production*/
SET @.Resp = 0
COMMIT TRANSACTION
RETURN (0)
END
PRINT 'Commpletely sold out' /*just for a visual; remove before use in
production*/
SET @.Resp = 3
GO
---
select * from #EventTickets
go
/*not enough tickets*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 1000,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
/*start the reservations!*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 526,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 87,
@.EventId = 10,
@.NumberReqd = 3,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 800,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 5,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 1,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
/*RESET*/
-- UPDATE #EventTickets SET TicketStatus = 0, ReserveUserID = NULL,
ReserveDate = NULL
-- drop proc FindAndReserve
Keith
"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411160632.1c24a330@.posting.google.c om...
> Hi all,
> I have the following sproc which is not giving me the results I would
> anticipate it should. Basically what the logic needed is that there
> are a load of tickets for an event, I need a sproc that holds (by
> setting a database flag) any number of tickets the users want, this is
> a true multi user environment. This sproc holds the required mount and
> if there are not enough tickets then it reports that but in a 2 user
> test, say for 4 tickets each, and where there are loads of tickets,
> the first user gets 4, the other gets a message saying there are not
> enough. Is it to due with viariable sharing or soemthing
> The idea was to loop round the database for available tickets, update
> the flag and once done commit the transaction, otherwise roll it back
> Any thoughts?
> Cehers
> Shaun
>
> CREATE PROCEDURE BIP_Tickets @.UserId Integer,
> @.EventId Integer,
> @.NumberReqd as Integer,
> @.Resp Integer OUTPUT AS
> SET NOCOUNT ON
> Declare @.CurrentTicket Integer
> Declare @.TicketRef Integer
> SET @.CurrentTicket = 0
>
> BEGIN TRAN BIP
> WHILE @.CurrentTicket < @.NumberReqd
> BEGIN
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < (@.NumberReqd - @.CurrentTicket)
> GOTO None_Left
> SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
> FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId
> ORDER BY [Event Tickets].[Ticket Ref])
> UPDATE [Event Tickets]
> SET [Ticket status] = 1,
> [User Id] = @.UserId,
> [Status Updated] = GetDate()
> WHERE [Ticket Ref] = @.TicketRef
> SET @.CurrentTicket = @.CurrentTicket + 1
> CONTINUE
> END
> COMMIT TRAN BIP
> SET @.Resp = 0
> RETURN
> None_Left:
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> GO

HELP ME TO OPTIMIZE QUERY

Hello friends,

I'm facing performance related problem while running following query on SQL Server 2000.

This query is basically used to find last location of each unit that are passed. Here I am passing data like "'26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @.Units variable. But it takes too much time and I don't get output. Table is having around5 Million records.

Query:

SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM tblUnit INNER JOIN

tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID

WHERE tblUnitHistory.UnitHistoryDate

IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ') GROUP BY tblUnitHistory.UnitID)

AND tblUnit.UnitID in (' + @.Units + ')

ORDER BY tblUnit.UnitID

Table Structure:

UnitHistoryID int Primary Key

UnitID int

Location varchar(200)

Latitude decimal 9

Longitude decimal 9

Speed decimal5

BatteryVoltage decimal5

ReasonCode int

DistanceFromLastLocation decimal9

UnitHistoryDate datetime

Indexes:

1. Clustered Index on ColumnUnitID

2. Non-clustered Index on ColumnUnitHistoryDate

3. Non-clustered Index on ColumnUnitHistoryID

Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.

Regards,

Sandeep

try to limit processed records by:

'SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM (SELECT * FROM tblUnit

where

AND tblUnit.UnitID in (' + @.Units + '))tblUnit

INNER JOIN

(SELECT tblUnitHistory.UnitID , MAX(UnitHistoryDate) UnitHistoryDate FROM (SELECT * FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ')) tblUnitHistory GROUP BY tblUnitHistory.UnitID) tblUnitHistory

ON tblUnit.UnitID = tblUnitHistory.UnitID

ORDER BY tblUnit.UnitID'

I hope that it will work, The Idea is to limit number of records used in join to minimum.