SQL Query takes too long to run

[Joelo]

Please could someone tell me why the below SQL Query Statement take a long time run (slow in process).....It runs quite alright no Error messages.

When remove the following line:
strsql = strsql & " INNER JOIN JDE_CNLPRD.CNLPRDDTA.F00165 AS F on (Cast(WADOCO AS varchar(254)))=F.GDTXKY"
it runs fast, but when add the above line it runs slow


' Open Connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
tkey = "" & key & ""
strsql = "SELECT *, A.ONDATE as wadate, C.ONDATE as wadate1, D.ONDATE as wadate2, B.FANUMB, B.FAASID, E.SRISSUE, E.SRDOCO, F.GDTXKY, F.GDTXVC as text FROM JDE_CNLPRD.CNLPRDDTA.F4801"
strsql = strsql & " INNER JOIN CNL_JDEDB.dbo.F00365 AS C on WADRQJ=C.ONDTEJ"
strsql = strsql & " INNER JOIN CNL_JDEDB.dbo.F00365 AS D on WASTRT=D.ONDTEJ"
strsql = strsql & " INNER JOIN JDE_CNLPRD.CNLPRDDTA.F1201 AS B ON WANUMB=B.FANUMB"
strsql = strsql & " INNER JOIN JDE_CNLPRD.CNLPRDDTA.F4817 AS E on WADOCO=E.SRDOCO"
strsql = strsql & " INNER JOIN JDE_CNLPRD.CNLPRDDTA.F00165 AS F on (Cast(WADOCO AS varchar(254)))=F.GDTXKY"
strsql = strsql & " LEFT OUTER JOIN CNL_JDEDB.dbo.F00365 AS A on WATRDJ=A.ONDTEJ"
strsql = strsql & " WHERE [WADOCO]=" & tkey

'response.write strsql

 

[vongrunt]

Very likely CAST() in JOIN clause, but this is 6-JOIN query anyway, so many things can go please wait.

From this query it's hard to guess table/data type of column WADOCO.

Note: SELECT *, blah generates many columns you probably don't need.

Note 2: ugly field names .

 

[spazman]

Probably all the joins and it will also depend on the data in those tables.

If you are runng SQL2000 convert it to a view and index it.
Then just query the view, using a Stored Proc.