SQL Query

Ice__Heat

declare @p1 int

set @p1=1073741835

declare @p2 int

set @p2=180150027

declare @p5 int

set @p5=2

declare @p6 int

set @p6=1

declare @p7 int

set @p7=-1

exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT "RECNUM", "VEHICLE_TYPE_ID", "DESCRIPTION" FROM "dbo"."VHCLTYPE" WHERE ((("U_VEHICLE_TYPE_ID" = UPPER(''TRCK''))))',@p5 output,@p6 output,@p7 output

select @p1, @p2, @p5, @p6, @p7

This is some code an application throws at a SQL Server 2005 database. This code seems to be generated by the DataFlex MSSQL Driver.

Is it syntactically correct? I am concerned about: UPPER(' 'TRCK' ') (Don't let the font fool you, these are two single quotes, I spaced them for emphasis)

This is in the context of performance issues we're having. It keeps fetching these cursors and therefore peaking the processor.

lpope187

Syntactically, it is correct. You need to escape single quotes with an additional single quote when processing SQL statements like this. From the look of the query itself, my thoughts are it is unnecessarily calling UPPER and you should have an index on U_VEHICLE_TYPE_ID. Not having an index may be causing some of the performance issues.

The cursor part is what concerns me. I couldn't find sp_cursorprepexec in the BOL, but according to http://jtds.sourceforge.net/apiCursors.html it seems to be an undocumented stored procedure. My gut tells me it is specifically for drivers such as this. Looking at the parameters, it looks like it creates a dynamic, read-only cursor. The dynamic attribute is probably why performance sucks. IMO, it should be doing a fast-forward if all it expects to do is read data, but without knowing the internals of the driver/application I can't be sure.

Perhaps there is some configuration or tweak you can do with the DataFlex driver. At one time, I had the same issue with IBMs ODBC driver for DB2 and I had to dig to find the option to set the default cursor type to something other than dynamic.

Ice__Heat

All operations use these cursors, also queries I write myself. They are encapsulated into these cursors by the driver it seems.

Jeff_S

This makes an excellent WTF ... what a convoluted and inefficient way to simply select some values from a table!

All that is needed is a simple SELECT statement ....

ammoQ

@Jeff S said:

This makes an excellent WTF ... what a convoluted and inefficient way to simply select some values from a table!

All that is needed is a simple SELECT statement ....

Not enough enterprisey for them, I'd guess.

tcolvinMI

Cursors in SQL Server are known to be performance bottlenecks. Seems to me that you would want to take the path of least resistance and do the simpler thing, instead of the more complex. Im in agreement with a previous post, which states that this same thing can be accomplished by a simple SELECT statement, rather than making use of a cursor.

piptheGeek

If you run a SELECT statement and the client wants to see those results, are they not returned by the driver layer as a cursor? ADODB even let you chose the cursor location and type. ADO.NET DataReader is (I think) only a fast forward cursor.