Slow Executing Query Template

[sidnooradarsh]

Hi,

I have written a stored Procedure in SQL server 2000.

When I run the Stored Procedure in SQL analyzer it gives me back results in less than 20 Seconds,

but when I run the same Stored Procedure with same inputs in Query Template it returns resultset after 4 mins which is terrible slow.

Please note there are no errors, the output of Query template matches with Stored Procedure output but execution time is very very slow.

Please someone tell me what could be the reason and possible solutions

Regards,

Adarsh

[Former Member · Accepted solution]

Hi.

I might be off but a few suggestions. You could try and start a profiler while you are doing this, especially including number of reads and writes performed by SQL server.

I have seen cases where the execution time performed in SQL query analyser and other external clients differs a lot. And my theory is that in case of IO heavy Stored procedures or equal, this difference might show up. So do you have some figures about the amount of reads and writes performed by the SQL transaction? This can be found by the SQL profiler. The profiler might also give you the execution time of the SQL part at least.

BR

Poul.

[Former Member]

Hi Adarsh

I had faced the same problem . When I run the Stored Procedure in SQL analyzer it gives me back results in 1min, but from MII query it took 10 mins.

Actually my Stored Procedure was to be scheduled.So instead of calling the Stored Procedure in a Fixed Query, then do a transaction and schedule it from MII, I scheduled it directly from SQL server.

So my issue resolved.

[Former Member]

Do not assume that SQL Query Analyzer is giving you all of the results right away, if you have a really large dataset being returned from the stored procedure. SQL may be "paging" the results, whereas MII requires retrieving the entire resultset.

I suspect that what is happening is that you have a very, very large number of rows being returned.

[Former Member]

Hi Rick

yes from Stored Procedure, it was inserting 10,000 rows or more than this to database.

[Former Member]

Adarsh Sidnoor,

Can you give me some estimate of data volume? Is the stored procedure just reading data or updating SQL server data?

Can you try running same query template one/two times more after and check if 4 min runtime reduces for subsequent calls?

Do share your observations.

BR,

SB.

[sidnooradarsh]

Hi All,

Thanks for your inputs.

My Stored procedure returns 30 rows (each row corresponds to one machine) and 25 columns.

The stored procedure contains one cursor looping through 30 machine one at a time and has come queries which hits 2 views which are made out of joining 5-6 tables which corresponds to computation of data of size 80K to 1Million.

Tested several times both on SQL Analyser and MII and found below results everytime

SQL analyzer returns resultset in less than 20 Seconds

MII returns resultant after 4 Mins

Please Help!!

Regards,

Adarsh

[jcgood25]

I have no doubts that query analyzer should be quicker to display results, even if it is not paginating the results into the results grid window at the bottom like Rick suggested. What are you using as a stopwatch to time this? You are comparing apples to oranges when you compare a native client tool, potentially running locally on the sql server itself with MII in an external capacity through JDBC. If you are waiting for the results to show up in the browser in html format you are also seeing the conversion to XML data followed by the XSLT transformation by IllumRowsetHTML.xsl to turn the Rowsets/Rowset/Row data table into the formatted html. All of this incorporates potentials for increased wire time between database and client, and makes the direct time comparison somewhat invalid.

[sidnooradarsh]

Hi Jeremy,

I completely agree with you, but client expects me to achieve the execution time closer to execution time of Sql Analyzer.

Any alternative or possible solutions or design techniques to improve the execution time through xMII?

Does increasing the bandwidth between MII and DB helps?

Please suggest.

Regards,

Adarsh

[Former Member]

What is the RowCount property set to in the Query Template? Set it to zero (0) and see if that changes the response time.

Also, how many rows are sent back when you run it in MII? Also 30?

[sidnooradarsh]

Hi,

I am getting better results now, I am getting execution time of MII and SQL Analyzer pretty much same that is less than 20 Seconds, the reason for this could be the client have recently cleared some bottlenecks in the network.

But I am keeping this thread open for some more time as I will be still monitoring the application closely.

Thanks to all for the guidance

Regards,

Adarsh

[sidnooradarsh]

Hi Experts,

I am back with this issue again.

The execution was good for quite sometime but now it seems that its taking way too long to execute the Stored Procedure from MII using Query Template.

From SQL Analyser for 2 Months large Data I get response in 18 Seconds but when I run same Stored Procedure in Query Template using same inputs the Execution time goes beyond 5 mins (earlier it was less than 3 mins when I started this thread)

I understand what Jeremy has suggested earlier in this thread but even if consider all odds still the execution time is very high more than 5 mins.

I don't know better place than this forum to get expert suggestion and solutions.

1) The DB and MII are in different servers but within same domain.

2) The DB is MSSQL Server 2000.

3) The Stored Procedure will process some 20 million records.

4) Returns 30 Rows of data always because there are 30 machines.

5) There is a Single DB for all 9 plants

6) We are using xMII 11.5.3 b66

These are connections settings in MII for DB

Connector: IDBC

ConnectorType: SQL

JDBC Driver: com.inet.tds.TdsDriver

PoolMax : 100

PoolSize : 5

Timeout : 2

UseCount: 256

WaitTime: 30

and some common inputs...

Is there anything I can do in terms of settings or bandwidth or any other technique?

Please provide me some guidance and help me out with this issue.

Regards,

Adarsh

[Former Member]

What if you run the stored procedure through a query tool (like Toad) on the MII server vs on the SQL server? I normally wouldn't suggest adding another variable into the mix.

[sidnooradarsh]

Hi Ryan,

This appears to be a good idea. I will check this out.

But here is the actual deal.

Initially we built report using BLS and when we generated report through BLS it used to take 5-10 mins for 2-4 months of large data.

So, in order to reduce the execution time we decided to use MSSQL server Stored Procedures but strangely with usage of Stored Procedure the Execution time worsened sometimes the application goes into some sort of infinite loop and never comes back and we have to forcefully end the process.

But when I run the same Stored Procedure with same inputs in SQL analyzer query tool boom, the output is in few seconds, every time I execute it executes without any infinite loops like in MII.

I had implemented Stored Procedures with other Clients but using Oracle and they are happy customers as this issue has never come up.

I am using a Cursor inside my SQL Stored Procedure which is looping through a maximum of 30 rows, I am wondering whether MII doesn't like MSSQL Cursor as they are resource consuming as compared against Oracle Cursors.

And I am also not sure whether we need to do any DB settings in MII.

Please suggest

Regards,

Adarsh

[Former Member]

ok, instead of returning many rows and columns, can you change the output of the query to be the number for rows (ie, count(*))?

This will use the same query logic, but will result in one row of results. This will determine if it is the query work or the query results that might be causing the slowness.

Also, you'll have to alias the count for MII, like

SELECT count(*) AS totalRows....

[Former Member]

Adarsh,

Go through some of the relative threads mentioned below:

-Suresh