Problem with locking in Selects

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

Regards

Bjrn(bjornsuneandersen@.gmail.com) writes:

Quote:

Originally Posted by

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.
>
The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.
>
I have also checked if there are any locks holding the other SPs back
- there isn't any.
>
So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>
|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.
|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/in...e.asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups
.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
--
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>