Re: WHERE IN() with parameters
From: Bernie Yaeger (berniey_at_cherwellinc.com)
Date: 11/18/04
- Next message: Paul Clement: "Re: MS Access w/ Insert + Select"
- Previous message: Scott M.: "Re: Property Builder"
- In reply to: Dave Fancher: "Re: WHERE IN() with parameters"
- Next in thread: Dave Fancher: "Re: WHERE IN() with parameters"
- Reply: Dave Fancher: "Re: WHERE IN() with parameters"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 18 Nov 2004 08:40:23 -0500
I don't know why I bother, but for a brief moment, I will.
1. The concept works equally well against ms access as it does against sql
server. I'm sure Tor would understand that.
2. I code in the real world, for real customers. I have made a very good
living doing it, and my customers wouldn't leave me for a second.
3. No (even though you haven't the courtesy to name it), I don't care about
SQL injection. It won't happen in my environment for reasons you can't
possibly understand.
4. The recompile is meaningless. My customers have no problem with the
daunting extra 10 seconds I cost them.
5. There is no concurrency issue, as they are locked out for those delicate
15 (total) seconds.
6.
> Every time this code is executed, both your application and database
> server
> are doing unnecessary work. It is obvious by the tone of your response
> that
> you do not value your users' (see also: customer) time since you have no
> problem with making them wait unnecessarily (e.g.: wasting their time)
> because of poor application design. If this server is being used by other
> applications, you are tying up resources that are probably better utilized
> by
> other processes.
Think about how academically insightful but practically stupid that is.
7. I imply nothing to Tor - I gave him a way of dealing with a common
problem.
Finally, you didn't have the brains to come up with the practical solution
that I did, so you have little else to do but debunk it.
Try the real world, Dave; you might have some success at it.
"Dave Fancher" <eijitek@comcast.net> wrote in message
news:6C5CB9F8-D0A3-47A6-B144-6036592D0FAC@microsoft.com...
> Since when does poor performance equate to good application design (did
> you
> not say that your method is "the best way")? When did good application
> design suddenly transform into nonsense?
>
> Perhaps you hear about this "nonsense" "all the time" because you need to
> learn some lessons about its importance in application design. In the
> "practical world," 15 seconds of unnecessary wait time is needlessly
> wasted
> time (for both the computer and the human) that could have been better
> spent
> elsewhere. I'm not at all arguing that entirely eliminating wait time is
> possible but it should always be minimized. (Also, don't forget to
> include
> network latency in determining total wait time.)
>
> Every time this code is executed, both your application and database
> server
> are doing unnecessary work. It is obvious by the tone of your response
> that
> you do not value your users' (see also: customer) time since you have no
> problem with making them wait unnecessarily (e.g.: wasting their time)
> because of poor application design. If this server is being used by other
> applications, you are tying up resources that are probably better utilized
> by
> other processes.
>
> You say that your method forces a recompile because the SP is different
> every time but in reality, this method does not force a recopile but
> rather
> forces a compile because it isn't that the SP is different but is a
> different
> SP altogether. (you dropped it, remember?) It may have a common name but
> it
> is a different SP.
>
> By posting this code in response to Tor's question, you [indirectly] claim
> that this method allows for a variable parameter list, but your SP isn't
> parameterized at all. Tor wanted to find an easy way to parameterize a
> dynamically generated IN clause of a query against an Access database.
> Completely ignoring the fact that your code is specific to SQL Server
> (and,
> as such, completely irrelevant to the question), where in "CREATE
> PROCEDURE
> sp_createa_rsummary AS ..." is a parameter list supplied? This method
> doesn't allow for a variable parameter list at all since you're still
> relying
> solely on string concatenation.
>
> Of course, relying solely on string concatenation rather than an actual
> parameterized query opens up potentially larger problems but I'll leave it
> up
> to you to discover what they are...perhaps your code is immune to them
> because this is "the best way?"
>
> Another point is the issue of concurrency. On top of the added overhead
> from additional executions, suppose you have two users that coincidentally
> execute this code within a fraction of a second of each other. User A's
> execution has just completed executing the code to create the procedure
> but
> has not yet executed the code to execute it (the SP). While User A's
> execution is in the state I just described, what happens when User B's
> code
> executes the drop procedure code?
>
> Since the code is in a try block, the catch code is executed and a message
> box is displayed telling the user that the procedure couldn't be found
> then
> continues to execute because you don't have any code to exit the function.
> Following the execution path, the SP for altering the table executes and
> throws another error which is caught and displays another error message.
> The
> function then assumes that everything worked correctly (which it didn't)
> and
> execution continues, most likely providing the user with incorrect
> results!
>
> Is it unlikely for this scenario to occur? Sure, but it is conceivable.
> This "solution" simply does not scale. As the user base grows, so does
> the
> probability that this problem will surface.
>
> Finally, why not use a view for the common part of the query? All that
> you're dynamically generating is the IN clause.
>
>
> "Bernie Yaeger" wrote:
>
>> Hi Dave,
>>
>> > Yikes!!!
>> > The code you provided is just laced with performance killers. Dropping
>> > and recreating stored procedures means updates to system tables.
>> Yes, you're correct. I lose a full second on this! Damn!
>>
>> > the SP is only ever being executed once. SPs are always compiled for
>> > their first execution so on top of the hit from updating the system
>> > tables, you're forcing a compile every time the SP executes.
>> That's because the sp is different every time. This too costs me, after
>> all, as you correctly point out, it's not compiled yet. This also can
>> cost
>> me anywhere from 2 - 10 seconds! Good God!
>>
>> Dave, I hear this performance nonsense all the time. In the practical
>> world, a function like this runs in 15 seconds. Give me a break.
>>
>> Bernie
>>
>> "Dave Fancher" <eijitek@comcast.net> wrote in message
>> news:EeudnRITf_PSSwfcRVn-jA@comcast.com...
>> > Yikes!!!
>> > The code you provided is just laced with performance killers. Dropping
>> > and recreating stored procedures means updates to system tables.
>> > Additionally, the SP is only ever being executed once. SPs are always
>> > compiled for their first execution so on top of the hit from updating
>> > the
>> > system tables, you're forcing a compile every time the SP executes.
>> >
>> > I would strongly suggest reading
>> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
>> > for some information about using SPs that execute dynamic SQL. Also
>> > note
>> > that using the Prepare() method of the SqlCommand class has a similar
>> > effect. Both of these methods avoid the constant updates to the system
>> > tables and will improve the operation of your application.
>> >
>> > On another, possibly moot point, this code doesn't apply to Tor's
>> > question. This code is specific to SQL Server while he's asking about
>> > the
>> > classes contained in System.Data.OleDb (OleDbCommand, OleDbParameter,
>> > etc...) and is referring to an Access database.
>> >
>> >
>> > "Bernie Yaeger" <berniey@cherwellinc.com> wrote in message
>> > news:uvTFaKEzEHA.2568@TK2MSFTNGP10.phx.gbl...
>> >> Hi Tor,
>> >>
>> >> Actually, I've found that the best way to do this is indeed to pass an
>> >> array. Now I know you can't, but you can, in effect, if you create
>> >> the
>> >> sp 'on the fly'. What I do is I first delete the sp; then I create a
>> >> bulkstring to contain my array - '12345', '34225', '957433', etc.
>> >> Then I
>> >> construct the sp, create it from the vb code, and run it. Below is an
>> >> example of one such function I use.
>> >>
>> >> HTH,
>> >>
>> >> Bernie Yaeger
>> >> Public Function createa_rsummary(ByVal marraylist As ArrayList, ByVal
>> >> oconn As SqlConnection) As Integer
>> >>
>> >> createa_rsummary = 0
>> >>
>> >> Dim i As Integer
>> >>
>> >> Dim bulkstring As String
>> >>
>> >> Dim acount As Integer = 0
>> >>
>> >> For i = 0 To marraylist.Count - 1
>> >>
>> >> acount += 1
>> >>
>> >> bulkstring += Chr(39) & Trim(marraylist(i)) & Chr(39) & ","
>> >>
>> >> If i <> marraylist.Count - 1 Then ' ie, it isn't the last item
>> >>
>> >> If acount > 20 Then
>> >>
>> >> acount = 0
>> >>
>> >> bulkstring += vbCrLf
>> >>
>> >> End If
>> >>
>> >> End If
>> >>
>> >> Next
>> >>
>> >> bulkstring = Mid(bulkstring, 1, bulkstring.Length - 1) & ")"
>> >>
>> >> Dim dcmd As New SqlCommand
>> >>
>> >> dcmd = New SqlCommand("sp_dropsp_createa_rsummary", oconn) ' drop the
>> >> sp
>> >>
>> >> dcmd.CommandType = CommandType.StoredProcedure
>> >>
>> >> Try
>> >>
>> >> dcmd.ExecuteNonQuery()
>> >>
>> >> Catch ex As Exception
>> >>
>> >> MessageBox.Show(ex.message)
>> >>
>> >> End Try
>> >>
>> >> Dim creationstring As String
>> >>
>> >> creationstring = "CREATE PROCEDURE sp_createa_rsummary AS " _
>> >>
>> >> & "if exists (select * from information_schema.tables where table_name
>> >> =
>> >> " _
>> >>
>> >> & "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _
>> >>
>> >> & "select imcacct, pubcode, invnum, inv_dt, brname, " _
>> >>
>> >> & "(case when inv_dt + 31 > getdate() then balance else 0 end) as
>> >> under31, " _
>> >>
>> >> & "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate()
>> >> then
>> >> balance else 0 end) as over30, " _
>> >>
>> >> & "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate()
>> >> then
>> >> balance else 0 end) as over60, " _
>> >>
>> >> & "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate()
>> >> then
>> >> balance else 0 end) as over90, " _
>> >>
>> >> & "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
>> >> over120" _
>> >>
>> >> & " into a_rsumtable from a_r where imcacct" _
>> >>
>> >> & " in (" & bulkstring & vbCrLf _
>> >>
>> >> & "order by pubcode, imcacct"
>> >>
>> >> Dim sqladapt As New SqlDataAdapter
>> >>
>> >> sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)
>> >>
>> >> Try
>> >>
>> >> sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp
>> >>
>> >> Catch ex As Exception
>> >>
>> >> MessageBox.Show(ex.Message)
>> >>
>> >> End Try
>> >>
>> >> Dim ocmd As New SqlCommand
>> >>
>> >> ocmd = New SqlCommand("sp_createa_rsummary", oconn) ' execute the sp
>> >>
>> >> ocmd.CommandType = CommandType.StoredProcedure
>> >>
>> >> Try
>> >>
>> >> ocmd.ExecuteNonQuery()
>> >>
>> >> Catch ex As Exception
>> >>
>> >> MessageBox.Show(ex.message)
>> >>
>> >> End Try
>> >>
>> >> Dim acmd As New SqlCommand
>> >>
>> >> acmd = New SqlCommand("sp_altera_rsumtable", oconn) ' give new table a
>> >> pk
>> >>
>> >> acmd.CommandType = CommandType.StoredProcedure
>> >>
>> >> Try
>> >>
>> >> acmd.ExecuteNonQuery()
>> >>
>> >> Catch ex As Exception
>> >>
>> >> MessageBox.Show(ex.message)
>> >>
>> >> End Try
>> >>
>> >> ' we now have a_rsumtable for only the selected accounts
>> >>
>> >> createa_rsummary = 1
>> >>
>> >> End Function
>> >>
>> >> "Tor Martin Halvorsen" <TorMartinHalvorsen@discussions.microsoft.com>
>> >> wrote in message
>> >> news:4C26A141-70F6-4B42-B24E-D2915603922D@microsoft.com...
>> >>> Hi all,
>> >>>
>> >>> Just for fun, I was investigating wheither I could use
>> >>> OleDbParameters
>> >>> in
>> >>> queries with WHERE IN() clauses.
>> >>>
>> >>> Given the code:
>> >>> ...
>> >>> cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
>> >>> int ids[] = {1, ... };
>> >>> for(int i=0; i<ids.Length; i++)
>> >>> cmd.Parameters.Add().Value = ids[ i ];
>> >>> ...
>> >>>
>> >>> After some hours with head scratching and banging I found that for
>> >>> every
>> >>> record I needed to get I had to add a new question-mark to the IN()
>> >>> clause.
>> >>> Now, this isn't a lot better than the good old string-concatenation i
>> >>> usually use
>> >>> since i have to figure out how many question-marks are needed in the
>> >>> clause.
>> >>>
>> >>> So is there a more elegant way to use OleDbParameters with IN
>> >>> clauses?
>> >>> Obviously passing an array to the parameter won't work, and a single
>> >>> question-mark will only use the first parameter.
>> >>>
>> >>>
>> >>> --
>> >>> :: The sniper at the gates of dawn ::
>> >>
>> >>
>> >
>> >
>>
>>
>>
- Next message: Paul Clement: "Re: MS Access w/ Insert + Select"
- Previous message: Scott M.: "Re: Property Builder"
- In reply to: Dave Fancher: "Re: WHERE IN() with parameters"
- Next in thread: Dave Fancher: "Re: WHERE IN() with parameters"
- Reply: Dave Fancher: "Re: WHERE IN() with parameters"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
