Timeout sql+asp

20 Jul 2006 8:15 AM

praeclarus

Hi,
I have a problema of query in an asp page, if I run the query in sql
query analizer is time running it's about 3-4 seconds, if I made the
same query in an asp page it create the error of timeout for the
browser. The result of the query output 2000 of records, all in one
page.

The query is the following:

Legend:
A table ordini_user refer to user
U table utenti refer ti prodoct requested
q table refer to product available

select distinct p.cognome, a.ord_ID, q.prod_id, q.descrip, u.qta
from ordini_user A
join ordini_utenti U on A.ord_id = U.ord_id and u.ord_id like
'62006___'
join utenti p on A.id_user = p.codcliente
and p.cognome like '%Marco%'
join prodotto q on U.prod_id=q.prod_id
order by p.cognome, q.prod_ID

the asp code is the following

Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.open "PROVIDER=SQLOLEDB;DATA
SOURCE=test_sql;UID=test;PWD=blank;DATABASE=Test"
set rs1=Server.CreateObject("ADODB.Recordset")

sql1="select distinct p.cognome, a.ord_ID, q.prod_id, q.descrip, u.qta
" &_
"from ordini_user A "&_
"join ordini_utenti U on A.ord_id = U.ord_id "&_
"and a.ord_id like '"&cod_ordine&"%' "&_
"join utenti p on A.id_user = p.codcliente "&_
"and p.cognome like '%"&fname&"%' join prodotto q on
U.prod_id=q.prod_id "&_
"order by p.cognome, q.prod_ID"

response.write(sql1)

rs1.Open sql1, conn1

do while not rs1.EOF
                                strtable = strtable & "<tr>" &_
                                "<td><input type='hidden' name='Cod'
value=" & rs1(0)
& "></td>" &_
                                "<td bgcolor='#D0F1FF'><div
align='center'><font

color='#000000'>" & rs1(0) & "</font></div></td>" &_

"<td bgcolor='#D0F1FF'><div
align='center'><font

color='#000000'>" & rs1(1) & "</font></div></td>" &_

"<td bgcolor='#D0F1FF'><div
align='center'><font

color='#000000'>" & rs1(2) & "</font></div></td>" &_

"<td bgcolor='#D0F1FF'><div
align='center'><font

color='#000000'>" & rs1(3) & "</font></div></td>" &_

"<td bgcolor='#D0F1FF'><div
align='center'><font

color='#000000'>" & rs1(4) & "</font></div></td>" &_

"</font></tr>"

Ho can I optimize or modify the output source consider that I want to
print all these records?

20 Jul 2006 8:38 AM

ML

Please post proper DDL. Where are the keys? Where are the indexes?

Are you absolutely sure your model is properly normalized? Why are you using
LIKE to join sets - don't you have proper keys?

ML

---
http://milambda.blogspot.com/

20 Jul 2006 8:51 AM

praeclarus

If I run the query in sql query analizer it spend 2 or 3 seconds, so I
think that it's normalized.
I use the like condition because I can write the MIKE name and it will
return me all the records that contains inside the name MIKE.

Thanks in advance for your interesting,

Daniele

ML ha scritto:

Show quote

> Please post proper DDL. Where are the keys? Where are the indexes?
>
> Are you absolutely sure your model is properly normalized? Why are you using
> LIKE to join sets - don't you have proper keys?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

20 Jul 2006 9:14 AM

ML

I don't have a clue about your server but 2-3 seconds for 2000 rows sounds
like a lot of time.

Anyway, normalisation and performance are two entirely different issues (not
isolated, but different). A properly normalised model may improve
performance, but this is only one of many elements that affect performance.

Please post DDL, sample data (maybe expected results). Read this for details:
http://www.aspfaq.com/etiquette.asp?id=5006

When using the LIKE operator the optimizer will be able to use the index for
prefix searches ('Mike%' or 'Mike') while suffix searches ('%Mike%' or
'%Mike') will lead to a scan. Of course, the index must be present.

My doubts whether the model is properly normalised are based on this
condition in your query:

u.ord_id like '62006___'

I'm not sure of the way your keys are generated, but this condition makes
little sense to me. Well, we might help you better after seeing the actual
DDL.

ML

---
http://milambda.blogspot.com/

20 Jul 2006 9:56 AM

Daniel Crichton

praecla***@libero.it wrote on 20 Jul 2006 01:15:52 -0700:

Show quote

> Hi,
> I have a problema of query in an asp page, if I run the query in sql
> query analizer is time running it's about 3-4 seconds, if I made the
> same query in an asp page it create the error of timeout for the
> browser. The result of the query output 2000 of records, all in one
> page.
>
> The query is the following:
>
> Legend:
> A table ordini_user refer to user
> U table utenti refer ti prodoct requested
> q table refer to product available
>
> select distinct p.cognome, a.ord_ID, q.prod_id, q.descrip, u.qta
> from ordini_user A
> join ordini_utenti U on A.ord_id = U.ord_id and u.ord_id like
> '62006___'
> join utenti p on A.id_user = p.codcliente
> and p.cognome like '%Marco%'
> join prodotto q on U.prod_id=q.prod_id
> order by p.cognome, q.prod_ID
>
> the asp code is the following
>
> Set conn1 = Server.CreateObject("ADODB.Connection")
> conn1.open "PROVIDER=SQLOLEDB;DATA
> SOURCE=test_sql;UID=test;PWD=blank;DATABASE=Test"
> set rs1=Server.CreateObject("ADODB.Recordset")
>
> sql1="select distinct p.cognome, a.ord_ID, q.prod_id, q.descrip, u.qta
> " &_
> "from ordini_user A "&_
> "join ordini_utenti U on A.ord_id = U.ord_id "&_
> "and a.ord_id like '"&cod_ordine&"%' "&_
> "join utenti p on A.id_user = p.codcliente "&_
> "and p.cognome like '%"&fname&"%' join prodotto q on
> U.prod_id=q.prod_id "&_
> "order by p.cognome, q.prod_ID"
>
> response.write(sql1)
>
> rs1.Open sql1, conn1
>
> do while not rs1.EOF
>                                 strtable = strtable & "<tr>" &_
>                                 "<td><input type='hidden' name='Cod'
> value=" & rs1(0)
> & "></td>" &_
>                                 "<td bgcolor='#D0F1FF'><div
> align='center'><font
> color='#000000'>" & rs1(0) & "</font></div></td>" &_
>                                 "<td bgcolor='#D0F1FF'><div
> align='center'><font
> color='#000000'>" & rs1(1) & "</font></div></td>" &_
>                                 "<td bgcolor='#D0F1FF'><div
> align='center'><font
> color='#000000'>" & rs1(2) & "</font></div></td>" &_
>                                 "<td bgcolor='#D0F1FF'><div
> align='center'><font
> color='#000000'>" & rs1(3) & "</font></div></td>" &_
>                                 "<td bgcolor='#D0F1FF'><div
> align='center'><font
> color='#000000'>" & rs1(4) & "</font></div></td>" &_
>                                 "</font></tr>"
>
> Ho can I optimize or modify the output source consider that I want to
> print all these records?

The obvious mistake I've seen during my years of ASP development is a missed
..MoveNext on the recordset in the Do ... Loop section. As you conveniently
didn't include the code at the end of the loop there's no way to determine
if this is the problem.

Dan

20 Jul 2006 1:12 PM

praeclarus

Hi,
I have mistake to copy the lasts two lines, these are the end of the
code:

rs1.MoveNext
loop

strtable= strtable & "</table>"

end if

Thanks for your help.

Daniele
Daniel Crichton ha scritto:

Show quote

> praecla***@libero.it wrote on 20 Jul 2006 01:15:52 -0700:
>
> > Hi,
> > I have a problema of query in an asp page, if I run the query in sql
> > query analizer is time running it's about 3-4 seconds, if I made the
> > same query in an asp page it create the error of timeout for the
> > browser. The result of the query output 2000 of records, all in one
> > page.
> >
> > The query is the following:
> >
> > Legend:
> > A table ordini_user refer to user
> > U table utenti refer ti prodoct requested
> > q table refer to product available
> >
> > select distinct p.cognome, a.ord_ID, q.prod_id, q.descrip, u.qta
> > from ordini_user A
> > join ordini_utenti U on A.ord_id = U.ord_id and u.ord_id like
> > '62006___'
> > join utenti p on A.id_user = p.codcliente
> > and p.cognome like '%Marco%'
> > join prodotto q on U.prod_id=q.prod_id
> > order by p.cognome, q.prod_ID
> >
> > the asp code is the following
> >
> > Set conn1 = Server.CreateObject("ADODB.Connection")
> > conn1.open "PROVIDER=SQLOLEDB;DATA
> > SOURCE=test_sql;UID=test;PWD=blank;DATABASE=Test"
> > set rs1=Server.CreateObject("ADODB.Recordset")
> >
> > sql1="select distinct p.cognome, a.ord_ID, q.prod_id, q.descrip, u.qta
> > " &_
> > "from ordini_user A "&_
> > "join ordini_utenti U on A.ord_id = U.ord_id "&_
> > "and a.ord_id like '"&cod_ordine&"%' "&_
> > "join utenti p on A.id_user = p.codcliente "&_
> > "and p.cognome like '%"&fname&"%' join prodotto q on
> > U.prod_id=q.prod_id "&_
> > "order by p.cognome, q.prod_ID"
> >
> > response.write(sql1)
> >
> > rs1.Open sql1, conn1
> >
> > do while not rs1.EOF
> >                                 strtable = strtable & "<tr>" &_
> >                                 "<td><input type='hidden' name='Cod'
> > value=" & rs1(0)
> > & "></td>" &_
> >                                 "<td bgcolor='#D0F1FF'><div
> > align='center'><font
> > color='#000000'>" & rs1(0) & "</font></div></td>" &_
> >                                 "<td bgcolor='#D0F1FF'><div
> > align='center'><font
> > color='#000000'>" & rs1(1) & "</font></div></td>" &_
> >                                 "<td bgcolor='#D0F1FF'><div
> > align='center'><font
> > color='#000000'>" & rs1(2) & "</font></div></td>" &_
> >                                 "<td bgcolor='#D0F1FF'><div
> > align='center'><font
> > color='#000000'>" & rs1(3) & "</font></div></td>" &_
> >                                 "<td bgcolor='#D0F1FF'><div
> > align='center'><font
> > color='#000000'>" & rs1(4) & "</font></div></td>" &_
> >                                 "</font></tr>"
> >
> > Ho can I optimize or modify the output source consider that I want to
> > print all these records?
>
>
> The obvious mistake I've seen during my years of ASP development is a missed
> .MoveNext on the recordset in the Do ... Loop section. As you conveniently
> didn't include the code at the end of the loop there's no way to determine
> if this is the problem.
>
> Dan

20 Jul 2006 1:21 PM

Daniel Crichton

praecla***@libero.it wrote on 20 Jul 2006 06:12:27 -0700:

> Hi,
> I have mistake to copy the lasts two lines, these are the end of the
> code:
>
> rs1.MoveNext
> loop
>
> strtable= strtable & "</table>"
>
> end if
>
> Thanks for your help.

OK, so it's not a missing .MoveNext. But further up the page, do you have an
On Error Resume Next or similar line? If so, and there's an error in that
loop, then you run the risk of it getting stuck if it can't move to the next
record for some reason - the rs1.EOF expression will never be true. Change
the start of your loop to:

Do While Not rs1.EOF And Err.Number = 0

and see if that makes a difference.

I'd also suggest commenting out the loop and putting just a response.write
"done" in to see if the recordset is opening quickly - if you still get a
timeout issue then it suggests that it's the initial query that's taking too
long, not the loop.

I'd also include Response.Buffer = False at the top of the page - if you
have response buffering enabled and the loop takes a while, it can cause the
browser to assume that there is no response and show a timeout error.

What is the exact message that you are seeing in the browser when this
errors?

Dan