Both.
Performance suffers due to:
- vbscript's deficiencies at string concatenation
- the extra processing caused by the need to deal with dealing with
delimiter characters (e.g., using Replace to escape literal quotes)
This also makes dynamic sql harder to use
- the lack of strong data typing
- extra network traffic due to sending long query strings across the wire
- the lack of encapsulation due to the failure to use stored procedures -
E.G., you need to create a parent record and a child record. Using
dynamic sql, you have to run the query to create the parent record,
retrieve the new ID, then run another query to insert the child record:
2-3 trips to the database. With a stored procedure, only a single trip
to the database is needed
- the need for SQL Server to compile query plans on demand
- there's more but I'm running out of time
The sinister aspect is the fact that SQL Injection is only possible when
dynamic sql is used. Here is some information about sql injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23 http://www.nextgenss.com/papers/adva..._injection.pdf http://www.nextgenss.com/papers/more..._injection.pdf http://www.spidynamics.com/papers/SQ...WhitePaper.pdf
Bob Barrows
Dan Nash wrote:
Bob,
Just interested.. why are dynamic queries not recommended? Performance
issues, or something more sinister?
Cheers
Dan
"Bob Barrows [MVP]" wrote:
This goes back to the point I'm always trying to hammer home in these
newsgroups:
Do not try to create and test queries from ASP. Always create and
test them using the query execution tool provided by your database
(Query Analyzer for SQL Server). Then, if you must use dynamic sql
(not recommended), you can simply copy/paste a working sql statement
from QA into your vbscript code and modify it as needed.
Bob Barrows
WC Justice wrote: This worked:
intBrokerCount = conn.execute("SELECT COUNT (BrokerID) FROM
tblBrokers")
response.write intBrokerCount(0) & " Brokers"
Thanks for your help
"Evertjan." <ex**************@interxnl.net> wrote in message
news:Xn********************@194.109.133.29...
WC Justice wrote on 12 aug 2004 in
microsoft.public.inetserver.asp.general:
> intBrokerCount = conn.execute "SELECT COUNT (BrokerID) FROM
> tblBrokers"
I would clear the space between COUNT and (BrokerID)
> response.write intBrokerCount & " Brokers"
I would write:
response.write intBrokerCount(0) & " Brokers"
Will this help?
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.