Re: SQL Statement or Cursor
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 03/26/04
- Next message: Alan Brewer [MSFT]: "Re: Unicode datatypes and the 'N'"
- Previous message: oj: "Re: Joins & Cursors"
- In reply to: Paul Ilacqua: "Re: SQL Statement or Cursor"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 27 Mar 2004 09:44:19 +1100
Hi Paul.
Thanks for the feed-back (:
Regards,
Greg Linwood
SQL Server MVP
"Paul Ilacqua" <pilacqu1@twcny.rr.com> wrote in message
news:eToQmD4EEHA.2732@tk2msftngp13.phx.gbl...
> 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
> > >
> > >
> >
> >
>
>
- Next message: Alan Brewer [MSFT]: "Re: Unicode datatypes and the 'N'"
- Previous message: oj: "Re: Joins & Cursors"
- In reply to: Paul Ilacqua: "Re: SQL Statement or Cursor"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
