Re: WHERE IN() with parameters
From: Dave Fancher (eijitek_at_comcast.net)
Date: 11/19/04
- Next message: jaYPee: "Re: stored procedure results"
- Previous message: John M Deal: "Re: stored procedure results"
- In reply to: Bernie Yaeger: "Re: WHERE IN() with parameters"
- Next in thread: Frans Bouma [C# MVP]: "Re: WHERE IN() with parameters"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 18 Nov 2004 20:11:02 -0800
The classes in the System.Data.SqlClient namespace work with Access? Since
when?
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.asp
http://searchvb.techtarget.com/ateQuestionNResponse/0,289625,sid8_cid571548_tax293673,00.html)
"CREATE PROCEDURE" works with Access? Since when?
(http://dbforums.com/t524759.html)
(ok, I found an example where CREATE PROCEDURE created an entry in a hidden
table in access and included it in the list of data sources but it was not
usable)
If you can provide an example of actual, functional code, I will gladly
concede the point.
You do imply that your code answers his question of easily parameterizing
dynamically built SQL. It does not use parameters therefore, it does not.
You might consider reading the other posts before you accuse me of not
having the brains to post a solution. I answered under the original thread
(at approximately the same time you responded directly) suggesting that he
use StringBuilders and some overloaded methods to not only dynamically build
a parameterized query but also to add the parameters with their values to it.
Unlike you, I don't insult your intelligence with personal attacks such as
"you didn't have the brains..." and "...for reasons you couldn't possibly
understand..." despite the fact that you posted irrelevant code and
continually assert statements that just aren't true. Also unlike you, in my
post I don't claim that my method is "the best" then refuse to refute any
claim about poor application design in terms of performance and security and
resort to personal attacks (see above) and statements such as:
"I hear this performance nonsense all the time"
"I code in the real world, for real customers" [Is this to assume that a
Fortune 500 company and it's supply base (900+ active, individual users)
isn't a "real customer?"]
How is optimizing processor time by minimizing useless processing
"academically insightful but practically stupid?" It is, simply put, good
practice. Also consider that many enterprises that use mainframes (and other
platforms) do indeed charge back the requesting organization for processor
time. Before you attack me [again] and lecture me about how good performance
is nonsense, understand that I'm not saying that this is the case with your
customers because I don't know if it is or not. I am saying though, that if
this is the case, you're costing them more money than they think...what's
worse is that if this is the case, they probably don't even realize it.
I too, do some freelance work in addition to my full time job (at above
mentioned company) and have found that the majority of customers are willing
to live with some degree of poor performance not because they have accepted
it but because they don't know any better. This is not to say that they're
"stupid" so don't take it as such. This is simply saying that they accept it
because they think they have to because they do not know the intracacies of
application design and therefore do not know any better. They often believe
that they do not have a choice. These customers simply do not care to learn
that much detail (I'm not saying that they should have to) but that is no
reason to provide them with a substandard product. It is my job to provide
my customer with a solid, high quality solution.
As support for my case I provide this simple example: I have a customer
that was willing to pay $40/hour to someone to design a web site that
consisted of graphics lifted from generic image galleries (ie: spinning gold
"welcome" signs and "drifting" envelopes) and Word Art. $40/hour is a lot of
money for low quality. Furthermore, this customer would most likely end up
paying even more later when he decided that the site should be redesigned
because it did not portray a professional image.
This particular customer is extremely intelligent and highly respected in
his field. He has been heavily involved in policy setting for a division of
Legal Services in Michigan and has been a ranking official in the county
court system in five Michigan counties. My customer has been engaged in
family law for nearly 30 years. He is, by far, not "stupid." He just does
not understand web development (again, nor should he have to) because it is
outside of the scope of his concerns.
The same holds true for all types of development be it for Web design or for
desktop applications. I respect my real world (to borrow another of your
phases) enough to provide them with a quality product. I will not penalize
them with a substandard product simply because they do not know the
technology. People hire me to provide them with the best product that I can
and I work for that money and their respect.
Until you can offer some evidence about how you've given "the best" (again,
your claim) solution rather than resort to personal attacks and other such
statements, you may want to take your own advice and "don't bother."
Until then, good luck to you, sir.
"Bernie Yaeger" wrote:
> 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: jaYPee: "Re: stored procedure results"
- Previous message: John M Deal: "Re: stored procedure results"
- In reply to: Bernie Yaeger: "Re: WHERE IN() with parameters"
- Next in thread: Frans Bouma [C# MVP]: "Re: WHERE IN() with parameters"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
