Problems with Server Side Cursor.

Question

Post reply

Problems with Server Side Cursor.

vijay_d

SSC Veteran

Points: 221
More actions
June 19, 2002 at 3:26 am

#105532

Hi all, I am running a SQL query which has joins on 2 tables, and performs SORT. The Server side cursor is enabled when this query is submitted through my java application. The number of records that I am handling here is around 60 million on the main table. This query works perfectly well, when i sort on a non-indexed column, but if I try to sort on an indexed column the query runs indefinitely, and never stops.
This same query runs good if I disable the Server side cursor. And works good even when the data size is less with server side cursor enabled. I am unable to figure out where the problem could be. Can anyone help me with this.
Following is my server config :
DataBase : SQLServer 2000 Standard Edition.
OS : Win 2000 Server
Disk Space : C(boot) - 4GB , D(Data) - 200GB, F(tempdb) - 40GB, E(trxn) - 40GB.
Database Size - 72GB on D, rest of the space is unused.
RAM size - 4GB
OS Swapping space - 8GB
Processors - 4 x 702 Mhz.
Looking forward to your response.
Thanks,
Vijay.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sjcsystems Ten Centuries Points: 1347 More actions · Answer 1

Hi Vijay

Could I ask a few questions on this:

Are you calling a stored procedure to extract your result set?, or is it a dynamic query? Are there parameters on the query? Could you perhaps post it for us to look at?

Is there an index which covers this query?

Regards

Simon

UK

crosspatch Hall of Fame Points: 3168 More actions · Answer 2

Hi Vijay

Yup we need some more info. I'm intrigued by your use of the words 'Server Side Cursor'. Are you submitting the SQL to SQLServer via JDBC and asking it to create a JDBC recordset? in which case the problem might lie with the Cursor rather than the database.

One thing I'd certainly do is ask SQLServer to explain the execution plan it's using for your Query, that might be very revealing on the use of indexes etc.

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

vijay_d SSC Veteran Points: 221 More actions · Answer 3

hi crosspatch,

I did try displaying the execution plan, if the serverside cursor is disabled, it will generate the plan, but if i enable it, there is no plan generated, i cant see how the query is being executed.

And yes I am using a JDBC bridge driver. below are the parameters to establish connection with the database.

db_drv=com.inet.tds.TdsDriver

db_url=jdbc:inetdae7:MC2001:1433?database=tc13&useCursorsAlways=true

"useCursorAlways=true" enables the serverside cursor

I dont think there is a way to simulate this in QueryAnalyser of SQLServer, coz there is no way to enable or disable the serverside cursor, but there are a few other Querying tools which allows to do this. one example is WinSQL available at

http://www.indus-soft.com/winsql

this is a free software, I tried to test my query with this.

Hope I have given you the right details that u asked for.

Thanks,

Vijay.

HI Simon, i am will send you the detail in while, sorry about the delay.

vijay_d SSC Veteran Points: 221 More actions · Answer 4

Hi Simon,

There is no stored procedure involved in this. And the field that I am trying to sort has an index on it. If i try to sort on anyother non-indexed column it works fine. Here is the query.

SELECT calls.deviceid AS SITE_ID, calls.duration AS DURATION, calls.dac AS DAC,

calls.dialdigit AS DIALED_DIGITS, Extension = CASE WHEN calls.billflag = 'E'

THEN calls.numtobill ELSE '' END, Authcode = CASE WHEN calls.billflag = 'A'

THEN calls.numtobill ELSE '' END, calls.outgrp AS OUT_GROUP, calls.outckt AS

OUT_CIRCUIT, cast(calls.cost as numeric(15,2)) AS COST, cclass.callclass AS

CALL_CLASS, calls.toloc AS TO_CITY_COUNTRY, calls.topostal AS TO_STATE,

calls.fromloc AS FROM_CITY_COUNTRY, calls.frompostal AS FROM_STATE,

convert(varchar(10), calls.billdate, 101) AS DATE, convert(varchar(8),

calls.billdate, 108) AS TIME

FROM calls WITH (NOLOCK) LEFT OUTER JOIN cclass

WITH (NOLOCK) ON calls.cllclassid = cclass.cclassid

WHERE (BILLDATE BETWEEN '2002-2-1 00:00:00' AND '2002-2-28 23:59:59')

AND (convert(char(8),BILLDATE,108) BETWEEN '00:00:00' AND '23:59:59')

AND (duration BETWEEN 0 AND 3599999) AND (cllclassid = 10)

AND (toloc LIKE 'IRAN') ORDER BY 4

PS: There is an index on the 4th column. If I remove the ORDER BY clause the query executes in normal time.

Thanks,

Vijay

crosspatch Hall of Fame Points: 3168 More actions · Answer 5

OK I think you might be trying to solve the wrong problem. Firstly the SQL you posted is what SQLServer actually runs so we need to verify that it is efficient. To do this we must interact with the database directly through Query Analyzer and evaluate the execution plan for the SQL as a straight query. I.E. no trace of JDBC or anything else.

Once we're satisfied with this then we need to determine what extra layers the JDBC bridge is placing in the form of Server Side Cursor. I'm no expert in JDBC but I would'nt mind betting that the bridge is interfering with the execution of the query.

The SQL you posted doesn't look too bad from a cursory glance, and from what you say it runs fine on it's own. If that's the case then maybe it's worth asking some searching questions about how the JDBC bridge is performing.

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

vijay_d SSC Veteran Points: 221 More actions · Answer 6

hi crosspatch,

I had tested this same query without using JDBC. I tried to test it using the ODBC driver provided by SQLServer, and executed from the querying tool (WinSQL), and it fails the same way. This doesnt look like a problem related to JDBC.

Vijay.

vijay_d SSC Veteran Points: 221 More actions · Answer 7

Has anyone got more information for this, pls let me know.

Vj