Re: SQL Statement or Cursor

From: Paul Ilacqua (pilacqu1_at_twcny.rr.com)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 17:22:56 -0500

Greg,

     Thanks for the very informative reply. My solution was to use VB / ADO
and call the initial recordset, then loop through and insert the TAG ID
number at each break in the sequence by concentating the SEQ , Part &
Location together. If they were not equal to the previous, increment the
counter. I am going against a several million row table and quickly found
out the performance hit of the set based approach. I guess I sometimes get
carried away looking for a T-SQL set based solution. With the VB route
within 30 minutes the entire program written and execution times are 60 - 90
seconds for the extraction and manipulations of the SQL Server data and
another 2 minutes for the Oracle insert of the resulting dataset.
Again I thank you for the non critical, very informative reply. That's how
we all learn, I'll hopefully never make this "mistake" again.

Regards
Paul

"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23HkvVdtEEHA.3980@TK2MSFTNGP09.phx.gbl...quickly found out
> Hi Paul.
>
> Your post seems to ask whether you're better off using a cursor or a sql
> statement for this purpose. You've got two responses that demonstrate how
> you can achieve this in a SQL Statement, but I'll offer a caution to you
on
> what's suggested already and an alternative procedural solution which
> *might* be better all up, depending on your actual objectives.
>
> If you're hell bent on writing a single SQL Statement for this, Joe &
> Anith's posts are probably your best options but all options suggested so
> far are very inefficient if you're working with large sets of data. This
is
> because there's a seriously high computational overhead to performing the
> internal aggregations that these statements require.
>
> Other rdbms provide rownum() functions that are designed to provide
support
> for ranking output from select. SQL Server's tsql doesn't have a rownum()
> function (as in plsql) and as such simply isn't designed to produce ranked
> output from SELECT efficiently. Instead, you have to use inefficient
kludges
> such as those suggested already.
>
> I'd suggest that your choice of SQL Statement or cursor for this should be
> evaluated against your objectives, including the answers to these
questions:
> (a) How important is writing portable (cross platform) code to you?
> (b) How important is performance of this code to you?
> (c) Will you run this code against large numbers of rows?
>
> If you answer "very important" to (a) but "not very important" to (b),
then
> the options provided to you already are probably right for you.
>
> If you answer "very important" to (b) but "not very important" to (a) and
> "yes" to (c) then you probably need another solution as you likely won't
be
> satisfied with the options suggested already after you see the performance
> results.
>
> Cursors aren't your only procedural answer. It's also possible to use temp
> tables or table variables to perform ranking work. In many situations
these
> can be more efficient when sorting output with more than a few hundred
rows.
> You simply include an identity(1, 1) column, insert yourt output (using
> order by) into the other columns and you get a sequence in the identity
> column. Of course, this is also a kludge in it's own way (procedural,
> proprietary) but you should understand that it's an available option that
> can far out-perform the set based options in tsql if you've got a lot of
> data to sort.
>
> I reguarly use combination of set based and procedural techniques when
> coding tsql. In plsql, its a no-brainer to just use rownum(), so I hope we
> get that one day in tsql. I find that my clients are usually more
interested
> in performance than immediate portability as this impacts them every day
and
> portability is a "maybe" that *might* impact them one day. Of course, this
> is their choice but understanding your options is very useful.
>
> There is a common mis-conception that single statement selects always
> perform faster than procedural code (eg cursors, loops & temp tables) but
> yours is one of those situations where this is not true in tsql, due to
> limited features in the language (no rownum()).
>
> HTH
>
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "Paul Ilacqua" <pilacqu1@twcny.rr.com> wrote in message
> news:OiogyyrEEHA.2768@tk2msftngp13.phx.gbl...
> > The following SQL Statement ..... returns the resultset as the bottom of
> > this post.
>
> --------------------------------------------------------------------------
> --
> > Select '27BARCODE' as 'LOC_CODE', Rack, PartNumber as 'Part',
> > Location, Line as 'DEPT', Count(*) as 'OH' From V_BC
> > Where Status = 'IR'
> > Group By Rack, PartNumber, Location, Line
> > Order By PartNumber
> >
>
> --------------------------------------------------------------------------
> --
> > What I need to add to the resultset is an incrementing number based on
a
> > starting seed value at every
> > Distinct PART, LOCATION and DEPT.
> > I.E. What I need to return is....
> >
> > Desired result set with incremented number.
> > LOC_CODE Rack SEQ Part Location
> > DEPT OH
> > --------- -------- -------------------- ---------- ---- -----------
> > 27BARCODE A0514834 400000 12815 MM25 2610 16
> > 27BARCODE A0516081 400000 12815 MM25 2610 16
> > 27BARCODE A0516090 400000 12815 MM25 2610 16
> > 27BARCODE A0516033 400001 12815 MM26 2610 16
> > 27BARCODE A0516012 400002 12816 MM26 2610 16
> > 27BARCODE A0516024 400002 12816 MM26 2610 16
> > 27BARCODE A0516078 400003 12818 MM26 2610 16
> > 27BARCODE A0516083 400004 12818 BB8 2610
> 16
> > 27BARCODE A0516044 400005 12818 B12
2610
> 16
> > 27BARCODE A0516011 400006 12818 B12
7620
> 16
> > 27BARCODE A0516029 400006 12818 B12
7620
> 16
> >
> >
> > Initial Result Set but lacking Incrementing number.
> >
> > LOC_CODE Rack Part Location DEPT OH
> > --------- -------- -------------------- ---------- ---- -----------
> > 27BARCODE A0514834 12815 MM25 2610 16
> > 27BARCODE A0516081 12815 MM25 2610 16
> > 27BARCODE A0516090 12815 MM25 2610 16
> > 27BARCODE A0516091 12815 MM26 2610 16
> > 27BARCODE A0516092 12815 MM26 2610 16
> > 27BARCODE A0516093 12815 MM26 2610 16
> > 27BARCODE A0516095 12815 MM26 2610 16
> > 27BARCODE A0516096 12815 MM26 2610 16
> > 27BARCODE A0422901 12816 MM25 2610 16
> > 27BARCODE A0422903 12816 MM25 2610 16
> > 27BARCODE A0435810 12816 MM25 2610 16
> > 27BARCODE A1377267 12818 RW100 2610 21
> > 27BARCODE A1377270 12818 RW100 2610 21
> > 27BARCODE A1377271 12818 RW100 2610 21
> > 27BARCODE A1377272 12818 RW100 2610 21
> > 27BARCODE A1377273 12818 RW100 2610 21
> > 27BARCODE A1381716 12818 RW100 2610 21
> > 27BARCODE A1381719 12818 RW100 2610 21
> > 27BARCODE A1381720 12818 RW100 2610 21
> > 27BARCODE A1404373 12819 RW100 2610 21
> > 27BARCODE A1407676 12819 AA12 2610 21
> > 27BARCODE A1407677 12819 RW100 2610 21
> > 27BARCODE A1407678 12819 RW100 2610 21
> > 27BARCODE A1407679 12819 RW100 2610 21
> >
> > Thanks Paul
> >
> >
>
>

Relevant Pages

  • Re: SQL Statement or Cursor
    ... Your post seems to ask whether you're better off using a cursor or a sql ... SQL Server's tsql doesn't have a rownum ... I'd suggest that your choice of SQL Statement or cursor for this should be ... > Initial Result Set but lacking Incrementing number. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL string problem
    ... clean up your parameters before sending them to SQL. ... > You must "escape" any single quotes when sending SQL statements ... > Just pass your SQL statement through this function when opening your ... >> Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)
  • Re: Populating a list -- table structure?
    ... this clears up a lot of issues and jargon with SQL. ... I think I'll have to try to get a query that will take the BKitIDs and the KitIDs and bring that information together. ... Queries (just shows the QBE grid for convenience -- ... and It really helps to use Aliases for tablenames as it makes the SQL statement shorter. ...
    (microsoft.public.access.forms)
  • Re: Emailing a Report
    ... CTRL-G to Goto the debuG window -- look at the SQL statement ... The Sub LoopAgmtsSendEmail is highlighted by the Debugger. ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL string problem
    ... You must "escape" any single quotes when sending SQL statements directly to ... Just pass your SQL statement through this function when opening your ... > Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)