Re: Processing queries simultaneously
From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 12/30/04
- Next message: raydan: "Re: RESTORE DATABASE: put data on DIFF FILES in a FILEGROUP"
- Previous message: Leila: "Re: SQL Server Instance"
- In reply to: Andrew J. Kelly: "Re: Processing queries simultaneously"
- Next in thread: Andrew J. Kelly: "Re: Processing queries simultaneously"
- Reply: Andrew J. Kelly: "Re: Processing queries simultaneously"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 30 Dec 2004 08:59:03 -0800
Thank you for your reply.
Thanks for the suggestion on the stored procedure. I think I might be able
to simplify
the SP because the user only requests in hours, so I can get rid of the
other conditions.
>Another question is where is the clustered index on the Packet table?
The clustered index is in Packet_contract, Packet_time. I find this
combination
to produce the fastest result.
>You do but not for this expensive query. Add a hint to the query of MAXDOP
>= 1 to these big queries so they only use one processor and leave the other
>for the other users.
If I force the query to use one processor, will it slow down this query ?
>Never do that. You are seriously hurting performance by fetching only a few
>rows at a time, not to mention the amount of extra network traffic. Get all
>the rows at once then do what ever processing you need or do more
>pre-processing in the back end and send fewer rows over to the client.
SQL Server is in the same machine with the VB server application that
request the data.
What I meant was from the VB program, I call the stored procedure to get all
the records. Then when the total number of characters of the data is >= 1000
(c_MAXOUT), I send the data to the client.
This is from my code:
sSql = "exec mySP " & "@sType='" & sType & "'," & "@sContract='" &
sContract & "'," & "@iNum=" & iNum
Set rs = New ADODB.Recordset
Set rs = m_adoCon.Execute(sSql)
Do While Not rs.EOF
sStr2 = "|" & rs("PACKET_DATA")
If Len(sStr2) + Len(sStr) < c_MAXOUT Then
sStr = sStr & sStr2
Else
sStr = Mid(sStr, 2) & "|"
FromClient.Send sStr
sStr = sStr2
End If
rs.MoveNext
Loop
Thank you.
"Andrew J. Kelly" wrote:
> >>That basically means you need to have a query and schema that will benefit
> >>from
> >>parallel processing first.
> > Would you please tell me what you meant by this ?
>
> In order for SQL Server to use more than 1 processor to process a request
> from a single user it must be something that can take advantage of parallel
> processing. For instance a if you are issuing a select that will use an
> index to retrieve just 5 rows with a seek there is little chance it will use
> multiple processors for such a simple operation. If you are scanning a
> clustered index on a large table it will most likely try and spawn multiple
> threads to read the data files in parallel.
>
> > CREATE procedure mySP
> > @sType varchar(1),
> > @sContract varchar(8),
> > @iNum smallint
> > as
> > SET NOCOUNT ON
> > if (@sType = 's')
> > select packet_data from packet where packet_time >= dateadd(second,
> > @iNum * -1, getdate()) and packet_contract = @sContract
> > else if (@sType = 'm')
> > select packet_data from packet where packet_time >= dateadd(minute,
> > @iNum * -1, getdate()) and packet_contract = @sContract
> > else if (@sType = 'h')
> > select packet_data from packet where packet_time >= dateadd(hour,
> > @iNum
> > * -1, getdate()) and packet_contract = @sContract
> > else if (@sType = 'd')
> > select packet_data from packet where packet_time >= dateadd(day,@iNum *
> > -1,CONVERT( CHAR(10), getdate(), 101)) and packet_contract = @sContract
> > SET NOCOUNT OFF
>
>
> I have several comments on this sp. First off I would probably create 4
> different sp's and call the appropriate one depending on the sType from the
> front end. This is due to the fact each one may have a different query plan
> (or some share the same ones). For instance there is a good chance that if
> you are only looking for a few seconds worth of data you can use an index in
> a Seek. Where as if you are looking for days worth it will probably scan an
> index or table. The plan may be dictated by the first call to the sp and
> subsaquent plans may use that even though they are improper.
> But in place of that I would use one query and calculate the date at the
> begining.
>
> if (@sType = 's')
> SET @DT = dateadd(second, @iNum * -1, getdate())
> if (@sType = 'm')
> SET @DT = dateadd(minute, @iNum * -1, getdate())
> etc. or use a CASE expression.
>
> Then you only have to list the query once. It makes for a lot more readable
> and maintainable code.
>
> Another question is where is the clustered index on the Packet table?
> Hopefully it is on the Packet_Time column. If not you seriously want to
> consider putting it there.
>
>
> > the 1st user's request to be completed. Because currently if 1st user's
> > request return 500K records, the 2nd user will have to wait until 1st
> > user's
> > request is completed before his request is being processed. I thought for
> > this I want to user more than 1 processor ??
>
> You do but not for this expensive query. Add a hint to the query of MAXDOP
> = 1 to these big queries so they only use one processor and leave the other
> for the other users.
>
>
> >>What is the client using to receive the result set?
> >>Hopefully it is not fetching them one row at a time.
> > I send data to the client a few records at a time (< 1000 characters), so
> > it
> > can be between 4-8 records at a time.
>
> Never do that. You are seriously hurting performance by fetching only a few
> rows at a time, not to mention the amount of extra network traffic. Get all
> the rows at once then do what ever processing you need or do more
> pre-processing in the back end and send fewer rows over to the client.
>
>
>
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
>
>
- Next message: raydan: "Re: RESTORE DATABASE: put data on DIFF FILES in a FILEGROUP"
- Previous message: Leila: "Re: SQL Server Instance"
- In reply to: Andrew J. Kelly: "Re: Processing queries simultaneously"
- Next in thread: Andrew J. Kelly: "Re: Processing queries simultaneously"
- Reply: Andrew J. Kelly: "Re: Processing queries simultaneously"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
