SQL Query



  • <FONT color=#0000ff size=2>

    declare</FONT><FONT size=2> @p1 </FONT><FONT color=#0000ff size=2>int

    set</FONT><FONT size=2> @p1</FONT><FONT color=#808080 size=2>=</FONT><FONT size=2>1073741835

    </FONT><FONT color=#0000ff size=2>

    declare</FONT><FONT size=2> @p2 </FONT><FONT color=#0000ff size=2>int

    set</FONT><FONT size=2> @p2</FONT><FONT color=#808080 size=2>=</FONT><FONT size=2>180150027

    </FONT><FONT color=#0000ff size=2>

    declare</FONT><FONT size=2> @p5 </FONT><FONT color=#0000ff size=2>int

    set</FONT><FONT size=2> @p5</FONT><FONT color=#808080 size=2>=</FONT><FONT size=2>2

    </FONT><FONT color=#0000ff size=2>

    declare</FONT><FONT size=2> @p6 </FONT><FONT color=#0000ff size=2>int

    set</FONT><FONT size=2> @p6</FONT><FONT color=#808080 size=2>=</FONT><FONT size=2>1

    </FONT><FONT color=#0000ff size=2>

    declare</FONT><FONT size=2> @p7 </FONT><FONT color=#0000ff size=2>int

    set</FONT><FONT size=2> @p7</FONT><FONT color=#808080 size=2>=-</FONT><FONT size=2>1

    </FONT><FONT color=#0000ff size=2>

    exec</FONT><FONT size=2> sp_cursorprepexec @p1 </FONT><FONT color=#0000ff size=2>output</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>@p2 </FONT><FONT color=#0000ff size=2>output</FONT><FONT color=#808080 size=2>,NULL,</FONT><FONT size=2>N</FONT><FONT color=#ff0000 size=2>'SELECT "RECNUM", "VEHICLE_TYPE_ID", "DESCRIPTION" FROM "dbo"."VHCLTYPE" WHERE ((("U_VEHICLE_TYPE_ID" = UPPER(''TRCK''))))'</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>@p5 </FONT><FONT color=#0000ff size=2>output</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>@p6 </FONT><FONT color=#0000ff size=2>output</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>@p7 </FONT><FONT color=#0000ff size=2>output

    select</FONT><FONT size=2> @p1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @p2</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @p5</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @p6</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @p7</FONT>

    <FONT size=2></FONT> 

    <FONT size=2>This is some code an application throws at a SQL Server 2005 database. This code seems to be generated by the DataFlex MSSQL Driver. </FONT>

    <FONT size=2>Is it syntactically correct? I am concerned about: <FONT color=#ff0000>UPPER(' 'TRCK' ')  </FONT><FONT color=#000000>(Don't let the font fool you, these are two single quotes, I spaced them for emphasis)</FONT></FONT>

    This is in the context of performance issues we're having. It keeps fetching these cursors and therefore peaking the processor.<FONT size=2>

    </FONT>


  • 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.

     



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



  • 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 ....

     



     



  • @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. 



  • 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.



  • 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.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.