Query/Report processing time

I am having a frustrating problem with a report. It is pulling data from a
stored procedure, and I have tested it through query analyzer and i get
performance times of 4-5 seconds. However, when I select this same sproc
through Reporting Services during report building time, and hit the "!" to
pull the data, punch in the exact same parameters, it is taking upward of
30-40 seconds to pull the same data. What is the deal? It makes no rational
sense.How much data are we talking about? In QA, enable Client Statistics and what are the # of rows
affected and # of bytes sent?
Also, which query designer are you using? Does it have 4 panes and show tables or is it the 2-pane
textbox/grid designer? QA and the designers all use different underlying mechanisms for querying
the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses the SQL Managed provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> Well, that is just it. It's NOT during the actual report processing that I
> am having this problem, it is when I am loading the dataset under the "Data"
> tab. When I hit the "!" and enter in my params, the thing is taking forever.
> But when I run the same sproc in Query Analyzer, it is taking 4-5 seconds.
> So, this has nothing to do with actual report processing, it is something it
> is doing when it is grabbing the data that is f'ed up (if you know what I
> mean). Hence, when I deploy this thing (although the report is really
> simple) since it is taking it forever to grab data that should take a few
> seconds, it is taking like 45 seconds to render this report. It is a real
> pain in the ass.
> "Andrew Byrne" wrote:
> > My guess is that in design time you are running the query through the .NET
> > IDE so I presume there is a bigger memory footprint required.
> >
> > That lag experienced is probably due to RS not caching the report between
> > runs, something which was improved in SP1.
> >
> > Again, I would say .NET is involved if you experience lag the first time (in
> > a session) you run a report - the CLR needs to load assemblies, everything is
> > running under IIS etc.
> >
> > Don't get me wrong - I agree that RS could be a lot faster - just merely
> > trying to come up with plausible reasons for the reduction in speed !
> >
> > "Dan Fell" wrote:
> >
> > >
> > > I too suffer from similar problems. I think the problem seems to lie in the
> > > rendering part of the reporting. The queries themselves take relatively
> > > little time but the report can take ages to appear on screen.
> > >
> > > Not only that but it can be quite erratic. There a lag when the Report
> > > Manager's pages and reports are first rendered and then they seem to render
> > > much quicker afterwards. Is this normal?
> > >
> > > DF
> > >
> > > "isamu" wrote:
> > >
> > > > I am having a frustrating problem with a report. It is pulling data from a
> > > > stored procedure, and I have tested it through query analyzer and i get
> > > > performance times of 4-5 seconds. However, when I select this same sproc
> > > > through Reporting Services during report building time, and hit the "!" to
> > > > pull the data, punch in the exact same parameters, it is taking upward of
> > > > 30-40 seconds to pull the same data. What is the deal? It makes no rational
> > > > sense.|||I have experienced the same thing, even though I am designing/running
directly on the server hosting RS.
It seems to be intermittent. I have had a report take 2-3 minutes
everytime. This could go on for hours, even after a reboot. Then, out of
nowhere it takes 3 seconds. FYI: The same query always took about 1
second through query analyzer. This seems to be an RS rendering issue...
Dan
___________________________
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:36026EB9-5AD5-4355-9C15-B7573A5DAB56@.microsoft.com...
> Argh ... well, this is quite odd, after working on this for about 2 hours
> yesterday and even running it a few times today, it appears to have fixed
> itself ... I am not sure, but perhaps this was some sort of Network issue
at
> my client's site. Thanks a lot for your time, and sorry to trouble you.
I
> guess if this issue ever comes up again, have the network guys check their
> latencies.
> "Donovan R. Smith [MSFT]" wrote:
> > How much data are we talking about? In QA, enable Client Statistics and
what are the # of rows
> > affected and # of bytes sent?
> >
> > Also, which query designer are you using? Does it have 4 panes and show
tables or is it the 2-pane
> > textbox/grid designer? QA and the designers all use different
underlying mechanisms for querying
> > the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses
the SQL Managed provider.
> >
> > --
> > Thanks.
> >
> > Donovan R. Smith
> > Software Test Lead
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "isamu" <isamu@.discussions.microsoft.com> wrote in message
> > news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> > > Well, that is just it. It's NOT during the actual report processing
that I
> > > am having this problem, it is when I am loading the dataset under the
"Data"
> > > tab. When I hit the "!" and enter in my params, the thing is taking
forever.
> > > But when I run the same sproc in Query Analyzer, it is taking 4-5
seconds.
> > > So, this has nothing to do with actual report processing, it is
something it
> > > is doing when it is grabbing the data that is f'ed up (if you know
what I
> > > mean). Hence, when I deploy this thing (although the report is really
> > > simple) since it is taking it forever to grab data that should take a
few
> > > seconds, it is taking like 45 seconds to render this report. It is a
real
> > > pain in the ass.
> > >
> > > "Andrew Byrne" wrote:
> > >
> > > > My guess is that in design time you are running the query through
the .NET
> > > > IDE so I presume there is a bigger memory footprint required.
> > > >
> > > > That lag experienced is probably due to RS not caching the report
between
> > > > runs, something which was improved in SP1.
> > > >
> > > > Again, I would say .NET is involved if you experience lag the first
time (in
> > > > a session) you run a report - the CLR needs to load assemblies,
everything is
> > > > running under IIS etc.
> > > >
> > > > Don't get me wrong - I agree that RS could be a lot faster - just
merely
> > > > trying to come up with plausible reasons for the reduction in speed
!
> > > >
> > > > "Dan Fell" wrote:
> > > >
> > > > >
> > > > > I too suffer from similar problems. I think the problem seems to
lie in the
> > > > > rendering part of the reporting. The queries themselves take
relatively
> > > > > little time but the report can take ages to appear on screen.
> > > > >
> > > > > Not only that but it can be quite erratic. There a lag when the
Report
> > > > > Manager's pages and reports are first rendered and then they seem
to render
> > > > > much quicker afterwards. Is this normal?
> > > > >
> > > > > DF
> > > > >
> > > > > "isamu" wrote:
> > > > >
> > > > > > I am having a frustrating problem with a report. It is pulling
data from a
> > > > > > stored procedure, and I have tested it through query analyzer
and i get
> > > > > > performance times of 4-5 seconds. However, when I select this
same sproc
> > > > > > through Reporting Services during report building time, and hit
the "!" to
> > > > > > pull the data, punch in the exact same parameters, it is taking
upward of
> > > > > > 30-40 seconds to pull the same data. What is the deal? It
makes no rational
> > > > > > sense.
> >
> >
> >

query with a table name that has a space...

I have a table name in SQL Server 2000 that has a space in it
ex: aim international

I had trouble just in the query analyzer with this..I had to place the
name in brackets [] for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query string. But is gives me this
error:
***************
Line 1: Incorrect syntax near 'AIM international'.
Exception Details: System.Data.SqlClient.SqlException: Line 1:
Incorrect syntax near 'AIM international'.
******************
Here is the string:
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
**********************

And the variable PubName is the string AIM international .
I tried placing it in brackets like in the query analyzer :
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &
"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
*******************

and I get this:
*******************
Invalid object name ''AIIM international''.
Exception Details: System.Data.SqlClient.SqlException: Invalid object
name ''AIIM international''.
*******************

Any idea what I have to do for it to work ? Can I use table names
with spaces or it's just not a good idea?
Thanks for the help guys!!
JMTHi,

> I have a table name in SQL Server 2000 that has a space in it
> ex: aim international
> I had trouble just in the query analyzer with this..I had to place the
> name in brackets [] for it to work. But now I'm in Visual Studio .Net
> 2003 and it gives me another problem. I get the table name from a drop
> down list selection and send it to a query string. But is gives me this
> error:
> ***************
> Line 1: Incorrect syntax near 'AIM international'.
> Exception Details: System.Data.SqlClient.SqlException: Line 1:
> Incorrect syntax near 'AIM international'.
> ******************
> Here is the string:
> ****************
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
> "' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "
> **********************
> And the variable PubName is the string AIM international .
> I tried placing it in brackets like in the query analyzer :
> ****************
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &

There's an extra single quote there: [' ... & pubname ...

> "'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "
> *******************
> and I get this:
> *******************
> Invalid object name ''AIIM international''.
> Exception Details: System.Data.SqlClient.SqlException: Invalid object
> name ''AIIM international''.
> *******************
> Any idea what I have to do for it to work ? Can I use table names
> with spaces or it's just not a good idea?

Well, it's not an good idea either :-)

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Well thanks alot Martijn,

Thoses extra quotes really made a difference. Now it all works!!
here's the new string:
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "

Beautiful!! I love it when things end up working!!
Thanks again!
JMT|||vbnetrookie (bigjmt@.hotmail.com) writes:
> Well thanks alot Martijn,
> Thoses extra quotes really made a difference. Now it all works!!
> here's the new string:
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
> "] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "

And now for title enter the following string:

' DROP TABLE [AIM International] --

As a safety precaution, make sure that you have a backup of your database
available.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm not sure what you mean :
enter the following string for title ?
WHat will this do and why add that line if it all works now?
I'm alaways willing to learn new stuff so i'm all ears!!
JMT|||vbnetrookie (bigjmt@.hotmail.com) writes:
> I'm not sure what you mean :
> enter the following string for title ?
> WHat will this do and why add that line if it all works now?
> I'm alaways willing to learn new stuff so i'm all ears!!

Presumably title comes from an input field. And it is into this input
field you should enter this string and learn what happens. You may
think you are searching for some data, but in fact you will blow away
your table.

This is something which is called "SQL injection". By entering SQL commands
into an input field, an intruder might be able to do things your database
that you did not intend. This is particular a danger if that input field
in on a web site. The trick is simple: Use an ' to close the string and
also a -- at the end to kill the syntax that comes after the query.

The remedy for this problem is simple: rather than building the complete
SQL statement, you use a parameterised statement:

Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode = @.postalcode And Title= @.title "

You then add the parameters with .AddParameter which I believe is on
the command object. (I'm not a very frequent ADO .Net programmer, so
I don't remember the details.) I encourage you to look it up. And I
cannot stress enough that this is essential stuff.

As you may note, I did not use a parameter for the table name; This is
because table names cannot be parameterized. Usually if you find the need
to determine the table name dynamically, this is a strong indiciation of
a poor database design.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> now for title enter the following string:
>' DROP TABLE [AIM International] --

Well, technically you'd need:
[AIM International]; DROP TABLE [AIM International] --

You have to finish that first SQL statement before you can in inject
new code.

I agree that if the table name is a parameter it indicates that the
database is not normalized, but if you *had* to do it that way then I'd
suggest putting the table names into an array and just passing as input
an index to the array (and of course doing bounds checking on it.)

Query window close automatically

It's very strange!! When i am querying SQL database via SQL Query Analyzer,
the query window sometimes close itself automatically without any notice.
There is no infomation in system event log.
Meanwhile, my other colleague can use the tool to query database without any
problem.
I am using SQL2000 SP3 on XP (SP1).
Is there anyone encountered the same problem? Could u please give us some
advise?
Thanks in Advance
Luke
Strange. I have not experienced this behavior.
Keith
"Luke" <luke.sheng@.sapb.com.cn> wrote in message
news:uMzEiShmEHA.2020@.TK2MSFTNGP09.phx.gbl...
> It's very strange!! When i am querying SQL database via SQL Query
Analyzer,
> the query window sometimes close itself automatically without any notice.
> There is no infomation in system event log.
> Meanwhile, my other colleague can use the tool to query database without
any
> problem.
> I am using SQL2000 SP3 on XP (SP1).
> Is there anyone encountered the same problem? Could u please give us some
> advise?
> Thanks in Advance
> Luke
>
sql

Query where feild name is reserved

Using query analyzer, how can I return a column (desc in this case)
that is also a reserved word?
I'm working with a commercial product so I can't change the name of
the column.
I've tried things like:
Select desc from foo
Select 'desc' from foo
select id,desc from foo
etc...
each one gives me: Error near reserved word 'desc'
Thanks.
When encountering any object name that is a reserved word, enclose it in
either double quotes, or square brackets.
"desc" or [desc]
And when you use multiple part names, such as:
MyDatabase.dbo.Table
enclose on the reserved word part of the name in delimiters:
MyDatabase.dbo.[Table]
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<Scamp@.nospam.com> wrote in message
news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>

Query where feild name is reserved

Using query analyzer, how can I return a column (desc in this case)
that is also a reserved word?
I'm working with a commercial product so I can't change the name of
the column.
I've tried things like:
Select desc from foo
Select 'desc' from foo
select id,desc from foo
etc...
each one gives me: Error near reserved word 'desc'
Thanks.ANSI SQL compliant:
SELECT "desc" FROM foo
SQL Server specific:
SELECT [desc] FROM foo
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Scamp@.nospam.com> wrote in message news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>|||When encountering any object name that is a reserved word, enclose it in
either double quotes, or square brackets.
"desc" or [desc]
And when you use multiple part names, such as:
MyDatabase.dbo.Table
enclose on the reserved word part of the name in delimiters:
MyDatabase.dbo.[Table]
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<Scamp@.nospam.com> wrote in message
news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>sql

Query very slow when executed using Reporting Services

Hello,
I have a dataset that uses a stored procedure as the data source. In
the query analyzer, it takes just over 2 seconds to execute. However,
in the execute query mode in the Visual Studio reports designer, the
query takes several minutes.
The report, when run from the server, also takes a very long time to
complete.
There are only 4 parameters, two varchar(2) and two datetime fields.
The particular query I am running returns 12 rows. There are no filters
applied to the report, not that would affect the query in the VS
reports designer.
Does anybody have any ideas on how to improve the performance?
Thank you,
Brian TakitaFirst the good news. You really did a good job with the information you
provided here. The bad news, this is very strange. The most common reasons
for slowness is lots of data returned (but you only have 12 rows), second is
having a filter. The issue with a filter isn't a filter per se but the fact
that all the data is brought over prior to the filter being applied. If you
execute the stored procedure in query analyzer and it returns 12 rows there
then something else is going on.
Do you have a large amount of fields being returned?
How is your data source credential setup? I am wondering if something is
going on with regards to your credentials that is causing the problem.
Do you have another report that uses the same data source? If so, how is its
performance. If not, then try some simple report that uses the same data
source so we can eliminate that as a possible problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112301864.710648.22590@.z14g2000cwz.googlegroups.com...
> Hello,
> I have a dataset that uses a stored procedure as the data source. In
> the query analyzer, it takes just over 2 seconds to execute. However,
> in the execute query mode in the Visual Studio reports designer, the
> query takes several minutes.
> The report, when run from the server, also takes a very long time to
> complete.
> There are only 4 parameters, two varchar(2) and two datetime fields.
> The particular query I am running returns 12 rows. There are no filters
> applied to the report, not that would affect the query in the VS
> reports designer.
> Does anybody have any ideas on how to improve the performance?
> Thank you,
> Brian Takita
>|||Thank you for the prompt reply.
>Do you have a large amount of fields being returned?
Yes. There are 40 fields returned.
>How is your data source credential setup? I am wondering if something
is
>going on with regards to your credentials that is causing the problem.
I'm using integrated security.
>Do you have another report that uses the same data source? If so, how
is its
>performance. If not, then try some simple report that uses the same
data
>source so we can eliminate that as a possible problem.
I tried creating a report using the stored procedure and it is also
slow.|||You can try to pin point what's taking so long. Look in the ReportServer
database; the ExecutionLog
table. It shows what report ran, when, the amount of time for data
retrieval, processing time, and rendering time.
It also shows what render format, render status (fail or success), query
row count and byte size of
the rendered file.
Adrian M.
MCP
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
>>Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
>>How is your data source credential setup? I am wondering if something
> is
>>going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
>>Do you have another report that uses the same data source? If so, how
> is its
>>performance. If not, then try some simple report that uses the same
> data
>>source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Here are the result fields:
TimeDataRetrieval: 255840
TimeProcessing: 45
TimeRendering: 34
Status: rsSuccess
ByteCount: 54070
RowCount: 0
What is strange is when the report is executed on the server, the
RowCount is 0, but when executed from the VS report designer, I get the
correct number of rows, but it is also slow.|||When I try pulling a very similar report with the same parameters as a
pdf file I get the following results:
TimeDataRetrieval: 228716
TimeProcessing: 224082
TimeRendering: 45
Status: rsSuccess
ByteCount: 1872
RountCount: 0|||Is it possible to you to do a test where the stored procedure is returning
fewer fields (for the test just have it do 5 fields for instance) and then
create a report off of that. Let's see if the problem is the number of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
> >Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
> >How is your data source credential setup? I am wondering if something
> is
> >going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
> >Do you have another report that uses the same data source? If so, how
> is its
> >performance. If not, then try some simple report that uses the same
> data
> >source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Thank you for your help Bruce. Your heuristics to solve the problem
were invaluable.
I solved the issue but did not find the ultimate source of the problem.
To solve the issue I optimized the stored procedure. I removed an
insert statement that scanned a fairly large table.
My stored procedure is fairly complicated as it uses several table
variables.
It strange that the sp would work fine using the query analyzer but not
using Sql Server RS.
I also tried recreating the sp, making all of the functions called in
the sp inline. There was a test case in the sp that took a long time to
complete.
Maybe the sp caused the server to run into memory constraints?
Thanks again,
Brian Takita|||Problem solved. The ExecutionLog table is something I'll need remember.
Thank you Adrian.|||You might want to turn on SQL profiler and look at the statements that are
generated by Query Analyzer and Reporting Services (through sqlclient).
Sometimes, the acutal execution syntax will be slightly different even when
the results are the same. This is primarily in the way that sqlclient
prepares parameters and executes the stored procedure. This difference may
cause SQL Server not to use a compiled query plan.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112313413.140441.295110@.f14g2000cwb.googlegroups.com...
> Thank you for your help Bruce. Your heuristics to solve the problem
> were invaluable.
> I solved the issue but did not find the ultimate source of the problem.
> To solve the issue I optimized the stored procedure. I removed an
> insert statement that scanned a fairly large table.
> My stored procedure is fairly complicated as it uses several table
> variables.
> It strange that the sp would work fine using the query analyzer but not
> using Sql Server RS.
> I also tried recreating the sp, making all of the functions called in
> the sp inline. There was a test case in the sp that took a long time to
> complete.
> Maybe the sp caused the server to run into memory constraints?
> Thanks again,
> Brian Takita
>

Query very slow via JDBC compared to Query Analyzer

I am trying out the JDBC driver SP3 for data access in a Java application,
and I find that some of the queries I had tested in Query Analyzer are
returning very slowly in my Java application. The queries not extremely
complex (just a couple joins), and they return small datasets (on the order
of 2-20 rows with 2 columns each).
In one example, I am waiting about 20 seconds for a query to return in my
Java application where it took less than one second in Query Analyzer.
I'm not really sure where to look to address these performance issues. Is
there something obvious I might have wrong on my connection string? I connect
to SQL Server over 100Mb Ethernet.
I'd appreciate any help!
Thanks
OK, it seems I did not choose very good search parameters when I looked for
answers previously... turns out the SendStringParametersAsUnicode as the
culprit. Sorry =) But thanks to those who posted previously.
"bbabey" wrote:

> I am trying out the JDBC driver SP3 for data access in a Java application,
> and I find that some of the queries I had tested in Query Analyzer are
> returning very slowly in my Java application. The queries not extremely
> complex (just a couple joins), and they return small datasets (on the order
> of 2-20 rows with 2 columns each).
> In one example, I am waiting about 20 seconds for a query to return in my
> Java application where it took less than one second in Query Analyzer.
> I'm not really sure where to look to address these performance issues. Is
> there something obvious I might have wrong on my connection string? I connect
> to SQL Server over 100Mb Ethernet.
> I'd appreciate any help!
> Thanks

Query Total Response Time

How can I know the total time (in miliseconds) for my SQL Server to answer a
query. I want to issue a query using an Query Analyzer and Measure the
Servers total response time. Thanks in advance!
On Wed, 2 Mar 2005 23:51:02 -0800, Borikoy wrote:

>How can I know the total time (in miliseconds) for my SQL Server to answer a
>query. I want to issue a query using an Query Analyzer and Measure the
>Servers total response time. Thanks in advance!
Hi Borikoy,
The simplest form is
SELECT CURRENT_TIMESTAMP
-- Insert your query here
SELECT CURRENT_TIMESTAMP
Slightly more advanced would be
DECLARE @.start datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert your query here
SET @.end = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @.start, @.end)
In both cases, the time used to push the rows to the client is included.
For a clean measurement of time taken to process the query, I usually
change all SELECT queries to SELECT INTO queries (so that the ooutput
goes in a temp table instead of over a possibly slow network, or is
slowed by display processing).
Another way to get information about time taken is:
SET STATISTICS TIME ON
-- Insert your query here
SET STATISTICS TIME OFF
Check out the description of SET STATISTICS TIME in Books Online. And
you might wish to read up on SET STATISTICS IO as well.
Whatever way you use, you won't get accurate comparisons if you run
queries with data in the cache. Often, the first execution of a query
takes longer because data needs to be brought into cache from hard disk;
subsequent invocations go lots faster because the data is still in
cache, so the slow hard disk access is not necessary anymore. To clear
out the data cache and the procedure cache as well (so you won't reuse
old execution plans), run the following statements before doing your
tests:
CHECKPOINT -- Write dirty pages to disk
DBCC DROPCLEANBUFFERS -- Flush all non-dirty pages from cache
DBCC FREEPROCCACHE -- Remove all cached execution plans
Running the above statements on a production database will severely
impact the performance of your running processes - but I assume that you
don't intend to run performance tests on a live production database
anyway!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

Query time-outs

SQL Server has (in its sp_configure) the option to set "remote query
timeout" and in Query Analyzer it can set the "Query time-out" option in
seconds.
My first question is:
Does the "remote query timeout" option only handle queries from remote
servers or does it work on queries directly conncected to the local SQL
Server? Meaning, does the "remote query timeout" option work like QA's
"Query time-out" on the server?
My next question is:
Depending on the answer from above, is there a way to configure the max
time for a given query?
The last question:
If SQL Server does not have a method for the above question, is there a
third party app that can limit the amount of time a query can take on
the SQL Server?
BillI think you have an option called CommandTimeOut in ADO. Using this, you can
set a limit in terms of the amount of time you want to wait for a query to
execute. If the time has elapsed, execution stops. You also have another
option called Query Governor Cost Limit, which determines uptil what cost a
query can execute. Every query has an execution cost and you can set a limit
on the maximum cost that is allowed for your application.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
news:400476BF.B971E8F1@.domail.maricopa.edu...

quote:

---

> SQL Server has (in its sp_configure) the option to set "remote query
> timeout" and in Query Analyzer it can set the "Query time-out" option in
> seconds.
> My first question is:
> Does the "remote query timeout" option only handle queries from remote
> servers or does it work on queries directly conncected to the local SQL
> Server? Meaning, does the "remote query timeout" option work like QA's
> "Query time-out" on the server?
> My next question is:
> Depending on the answer from above, is there a way to configure the max
> time for a given query?
> The last question:
> If SQL Server does not have a method for the above question, is there a
> third party app that can limit the amount of time a query can take on
> the SQL Server?
>
> Bill

---

|||That may be true, but I don't have influence on the Development team
because the fault was seen in Crystal. I've checked into the Query
Governor Cost Limit, but the cost (though documented in seconds) fails
to stop a query on a duration of time. In my test I set the Query cost
to 8 (seconds) and ran a simple (poor performance query) "Select * from
sysobjects A1, sysobjects A2". Though it is a cartesian product with a
runtime duration between 2 minutes to 8 minutes, the cost estimation in
query performance shows up as a 7. In my mind this query should never
run and I would never be allowed to govern the time a query takes if the
cost estimation isn't accurate to a closer number of the duration. So,
how is the cost derived and what can be done to limit query duration
from the server side?
Thank you,
Bill
SriSamp wrote:[QUOTE]
> I think you have an option called CommandTimeOut in ADO. Using this, you c
an
> set a limit in terms of the amount of time you want to wait for a query to
> execute. If the time has elapsed, execution stops. You also have another
> option called Query Governor Cost Limit, which determines uptil what cost
a
> query can execute. Every query has an execution cost and you can set a lim
it
> on the maximum cost that is allowed for your application.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
> news:400476BF.B971E8F1@.domail.maricopa.edu...

Query time-outs

SQL Server has (in its sp_configure) the option to set "remote query
timeout" and in Query Analyzer it can set the "Query time-out" option in
seconds.
My first question is:
Does the "remote query timeout" option only handle queries from remote
servers or does it work on queries directly conncected to the local SQL
Server? Meaning, does the "remote query timeout" option work like QA's
"Query time-out" on the server?
My next question is:
Depending on the answer from above, is there a way to configure the max
time for a given query?
The last question:
If SQL Server does not have a method for the above question, is there a
third party app that can limit the amount of time a query can take on
the SQL Server?
BillI think you have an option called CommandTimeOut in ADO. Using this, you can
set a limit in terms of the amount of time you want to wait for a query to
execute. If the time has elapsed, execution stops. You also have another
option called Query Governor Cost Limit, which determines uptil what cost a
query can execute. Every query has an execution cost and you can set a limit
on the maximum cost that is allowed for your application.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
news:400476BF.B971E8F1@.domail.maricopa.edu...
> SQL Server has (in its sp_configure) the option to set "remote query
> timeout" and in Query Analyzer it can set the "Query time-out" option in
> seconds.
> My first question is:
> Does the "remote query timeout" option only handle queries from remote
> servers or does it work on queries directly conncected to the local SQL
> Server? Meaning, does the "remote query timeout" option work like QA's
> "Query time-out" on the server?
> My next question is:
> Depending on the answer from above, is there a way to configure the max
> time for a given query?
> The last question:
> If SQL Server does not have a method for the above question, is there a
> third party app that can limit the amount of time a query can take on
> the SQL Server?
>
> Bill|||That may be true, but I don't have influence on the Development team
because the fault was seen in Crystal. I've checked into the Query
Governor Cost Limit, but the cost (though documented in seconds) fails
to stop a query on a duration of time. In my test I set the Query cost
to 8 (seconds) and ran a simple (poor performance query) "Select * from
sysobjects A1, sysobjects A2". Though it is a cartesian product with a
runtime duration between 2 minutes to 8 minutes, the cost estimation in
query performance shows up as a 7. In my mind this query should never
run and I would never be allowed to govern the time a query takes if the
cost estimation isn't accurate to a closer number of the duration. So,
how is the cost derived and what can be done to limit query duration
from the server side?
Thank you,
Bill
SriSamp wrote:
> I think you have an option called CommandTimeOut in ADO. Using this, you can
> set a limit in terms of the amount of time you want to wait for a query to
> execute. If the time has elapsed, execution stops. You also have another
> option called Query Governor Cost Limit, which determines uptil what cost a
> query can execute. Every query has an execution cost and you can set a limit
> on the maximum cost that is allowed for your application.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
> news:400476BF.B971E8F1@.domail.maricopa.edu...
> > SQL Server has (in its sp_configure) the option to set "remote query
> > timeout" and in Query Analyzer it can set the "Query time-out" option in
> > seconds.
> >
> > My first question is:
> > Does the "remote query timeout" option only handle queries from remote
> > servers or does it work on queries directly conncected to the local SQL
> > Server? Meaning, does the "remote query timeout" option work like QA's
> > "Query time-out" on the server?
> >
> > My next question is:
> > Depending on the answer from above, is there a way to configure the max
> > time for a given query?
> >
> > The last question:
> > If SQL Server does not have a method for the above question, is there a
> > third party app that can limit the amount of time a query can take on
> > the SQL Server?
> >
> >
> > Bill