HELP: Delayed Inserts in SQL Server!

Hi all,
I have a small table with the following columns:
Refno (int), CampaignID(int), Value (int)
Sometimes a record takes >30 - 40 seconds to write. Is there a way to
ensure that a record is written in a timely manner?
Other info: I do have a stored procedure which loops the table waiting
for a response:
WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
AND @.LOOPCOUNTER < 30
BEGIN
SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
/* Pause Loop Delay in hh:mm:ss:ms format */
WAITFOR DELAY '00:00:00.200'
END
Also, once a record is read, it is deleted from the table. Could the
deletes be locking the table?
Thank you for ANY help.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Your deletes and your looping SELECT can both be blocking the inserts.
Can you post DDL for the table, including any indexes and constraints?
Also, how many rows are in the table?
Your loop might be more efficient expressed as:
WHILE NOT EXISTS (SELECT * FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/|||"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
>
What version of SQL Server are you running? I am assuming 2000.
What indexes are on the table?
Why do you have a WHILE loop running? That is most likely the cause of your
problem. It is probably holding locks on the table while an INSERT is
attempting to grab locks.
Is the looping doing something for you? Some type of notification? Could
you use a trigger on INSERT to accomplish the same task without using the
loop?
Rick Sawtell
MCT, MCSD, MCDBA|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> Can you post DDL for the table, including any indexes and constraints?
> Also, how many rows are in the table?
Approximately 20 - 30 rows at a time. After a record is read, it is deleted
from the table.
There are no indexes and constraints on the table. I am running SQL Server
2000.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
The While loop is used for notification - An ASP script executes a stored
procedure which loops the table waiting for the insert. Once the insert is
completed, the loop will complete and the value is returned to the ASP
page.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:uoA11mcxEHA.2624@.TK2MSFTNGP11.phx.gbl:
> What version of SQL Server are you running? I am assuming 2000.
Yes, SQL Server 2000.
> What indexes are on the table?
There are no indexes - I dropped all indexes in hopes that the INSERT
query will run faster.
> Why do you have a WHILE loop running? That is most likely the cause
> of your problem. It is probably holding locks on the table while an
> INSERT is attempting to grab locks.
The While loop is used for notification - An ASP script executes a
stored procedure which loops the table waiting for the insert to
complete. Once the insert is completed, the loop will find the record
and the value is returned to the ASP page. If no value is found within X
seconds the SP exits and the default value returned.
> Is the looping doing something for you? Some type of notification?
> Could you use a trigger on INSERT to accomplish the same task without
> using the loop?
Do you know of any other way to pause the execution of a select
statement and wait for a notification? A trigger *would* be nice... but
the problem is that the value must be returned to an external process (a
VoiceXML server which can only call webpages):
Here is the DDL for the table in case it might help you.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||You could start by defining a primary key... Refno, perhaps?
And maybe ease up the SELECT loop a bit, and/or use a (NOLOCK) hint to
ensure that it doesn't hold locks on the table...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B95DA8324nntprogerscom@.140.99.99.130...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> > Can you post DDL for the table, including any indexes and constraints?
> > Also, how many rows are in the table?
> Approximately 20 - 30 rows at a time. After a record is read, it is
deleted
> from the table.
> There are no indexes and constraints on the table. I am running SQL Server
> 2000.
> CREATE TABLE [dbo].[DialAnswerLookup] (
> [campID] [int] NOT NULL ,
> [refno] [int] NOT NULL ,
> [answeredby] [int] NULL
> ) ON [PRIMARY]
> GO
>
> The While loop is used for notification - An ASP script executes a stored
> procedure which loops the table waiting for the insert. Once the insert is
> completed, the loop will complete and the value is returned to the ASP
> page.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/