Speeding up in-line queries

[Astra]

Hi All

I know this is a 'try it yourself' thing, but is it generally true that
in-line query (A):

strSQLQuery = "update accounts set fred='" & strfred & "',bob='" & strbob &
"', etc

is not pretty to read as one long string, BUT a lot faster to execute than
in-line query (B):

strSQLQuery = "update accounts set "
strSQLQuery = strSQLQuery & "fred='" & strfred & "',"
strSQLQuery = strSQLQuery & "bob='" & strbob & "',
etc etc

Yes?

Thanks

Robbie

[Bob Barrows [MVP]]

Astra wrote:

> Hi All
>
> I know this is a 'try it yourself' thing, but is it generally true
> that in-line query (A):
>
> strSQLQuery = "update accounts set fred='" & strfred & "',bob='" &
> strbob & "', etc
>
> is not pretty to read as one long string, BUT a lot faster to execute
> than in-line query (B):
>
> strSQLQuery = "update accounts set "
> strSQLQuery = strSQLQuery & "fred='" & strfred & "',"
> strSQLQuery = strSQLQuery & "bob='" & strbob & "',
> etc etc
>
> Yes?
>

It's faster, but not a LOT faster. You will only be able to notice the
difference in a loop that's building a huge string.

The real problem is the possibility of SQL Injection:
[url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url]
[url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
[url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

You should not be using dynamic sql. Stored procedures is the most secure
methodology.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron [SQL Server MVP]]

Other than the building of the string, there will be no difference in
executing (A) vs. (B). These queries will look identical to the database.

And I agree with Bob. STOP USING AD HOC SQL.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)





"Astra" <info@NoEmail.com> wrote in message news:40d348fe$1_4@127.0.0.1...

> Hi All
>
> I know this is a 'try it yourself' thing, but is it generally true that
> in-line query (A):
>
> strSQLQuery = "update accounts set fred='" & strfred & "',bob='" & strbob
> &
> "', etc
>
> is not pretty to read as one long string, BUT a lot faster to execute than
> in-line query (B):
>
> strSQLQuery = "update accounts set "
> strSQLQuery = strSQLQuery & "fred='" & strfred & "',"
> strSQLQuery = strSQLQuery & "bob='" & strbob & "',
> etc etc
>
> Yes?
>
> Thanks
>
> Robbie
>
>

[Laphan]

Hi Guys

Thanks for the feedback, but I've got use ad hoc queries because my db is
MySQL 4.

Can't afford SQL Server hosting at present, as I'm trying to get my foot in
the door with clients.

Rgds

Robbie

Aaron [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote in message
news:eHg2yjgVEHA.3420@TK2MSFTNGP12.phx.gbl...
Other than the building of the string, there will be no difference in
executing (A) vs. (B). These queries will look identical to the database.

And I agree with Bob. STOP USING AD HOC SQL.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)





"Astra" <info@NoEmail.com> wrote in message news:40d348fe$1_4@127.0.0.1...

> Hi All
>
> I know this is a 'try it yourself' thing, but is it generally true that
> in-line query (A):
>
> strSQLQuery = "update accounts set fred='" & strfred & "',bob='" & strbob
> &
> "', etc
>
> is not pretty to read as one long string, BUT a lot faster to execute than
> in-line query (B):
>
> strSQLQuery = "update accounts set "
> strSQLQuery = strSQLQuery & "fred='" & strfred & "',"
> strSQLQuery = strSQLQuery & "bob='" & strbob & "',
> etc etc
>
> Yes?
>
> Thanks
>
> Robbie
>
>

[Aaron [SQL Server MVP]]

> Thanks for the feedback, but I've got use ad hoc queries because my db is

> MySQL 4.

If you say up front what database platform / version you are using, you are
less likely to get irrelevant advice.

We can't remember what database platform every single user is using.
Especially when you switch randomly from astra to laphan and back again...

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Laphan]

Hi Aaron

Apols for the schizophrenia.

I really appreciate the advice from both of you and will try to remember to
confirm my setup.

Forgiven? :0)

Now about the best way to flit between http and https when end users like to
go back and forth....



Aaron [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote in message
news:uHBRZ3jVEHA.1472@TK2MSFTNGP09.phx.gbl...

> Thanks for the feedback, but I've got use ad hoc queries because my db is
> MySQL 4.

If you say up front what database platform / version you are using, you are
less likely to get irrelevant advice.

We can't remember what database platform every single user is using.
Especially when you switch randomly from astra to laphan and back again...

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)