Hairy SQL Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Hairy SQL Query help

Author

Topic

cyberdog
Starting Member

8 Posts

Posted - 2002-07-30 : 17:07:01

I take a search string and cop it into keywords. Then I check each keyword against several fields in one table. The result has to then be inner joined to a crosslink table that contains primary key indexes for 10 other tables. Where there are found matches all the inner joined tables loop through the recordsets one row at a time and display on an ASP page. I have created one massive inner join SQL query that works fine, but I would like some ideas on how I could improve the performance. Also, how would you put something like that in a SPROC? (SQL Guru I am not!)

Thanks,

John

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-30 : 17:25:28

Don't use a loop in your recordset, put the results into an array using the GetRows method, or, if the results will be formatted very basically, you can use GetString. Here's an example:

objConn.Open "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=pubs;User ID=myLogin;Password=password"
objRS.Open "SELECT * FROM authors", objConn
rowArray=objRS.GetRows()
objRS.Close
response.write "<table>"
For x=1 to Ubound(rowArray,2)
 response.write "<tr><td>" & rowArray(0, x) & "</td><td>" & rowArray(1, x) & "</td><td>" & rowArray(2, x) & "</td></tr>"
Next
response.write "</table>"

Alternately, you can replace the code in red with the following:

rowData=objRS.GetString(,,"</td><td>","</td></tr><tr><td>","")
objRS.Close
response.write "<table><tr><td>" & rowData & "</table>"

Using GetRows and GetString will vastly improve the performance of you web page, especially with large amounts of data.

As far as your SQL is concerned, without more details on the table structures and the SQL you have now, it'll be hard to say how it performs and whether it can be improved. If you post this detail we can take a crack at it.

cyberdog
Starting Member

8 Posts

Posted - 2002-07-30 : 18:21:41

I will skip the SELECT portion and post the inner joins that pull this all together.

Here is the worst part of it...

SQL = SQL & "FROM (tblMG INNER JOIN (tblB INNER JOIN tblMGL ON tblB.mID = tblMGL.mID) ON tblMG.mgID = tblMGL.mgID) INNER JOIN (tblS INNER JOIN (tblR INNER JOIN (tblPT INNER JOIN (tblCS INNER JOIN (tblCES INNER JOIN (tblBS INNER JOIN tblSL ON tblBS.bID = tblSL.bID) ON tblCES.eID = tblSL.eID) ON tblCS.csID = tblSL.csID) ON tblPT.ptID = tblSL.ptID) ON tblR.rID = tblSL.rID) ON tblS.sID = tblSL.sID) ON tblMGL.mgLinkID = tblSL.mgLinkID "

SQL = SQL & "WHERE (tblS.sDescription LIKE '%" & varSearchTerm & "%' "
SQL = SQL & "OR tblS.sKeywords LIKE '%" & varSearchTerm & "%') "
SQL = SQL & "AND tblB.mID <> 3 ORDER BY tblB.mID, tblS.sName ASC;"

*tblSL* contains all the primary key indexes for the bulk of the tables. In most of the tables I am just accessing the table index to find one field, usually a varchar string field. *tblS* is the main data table that's needed.

I figure there has to be a better way than using the above SQL string query, but I have yet to get anything else to work.

Plus, I am concerned even more if I allowed more than one Search term too.

Thanks for any advice.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-30 : 19:30:15

quote:
As far as your SQL is concerned, without more details on the table structures and the SQL you have now, it'll be hard to say how it performs and whether it can be improved. If you post this detail we can take a crack at it.

What we're looking for here is the actual DDL for the tables.

BTW, you don't need to nest your inner join statements. The rowset produced by:


w inner join ( x inner join ( y inner join z on y.id = z.id ) on x.id = y.id ) on w.id = x.id

is equivalent to:

w
inner join x on w.id = x.id
inner join y on x.id = y.id
inner join z on y.id = z.id

the latter is much easier to read.

Jonathan Boott, MCDBA
{0}

cyberdog
Starting Member

8 Posts

Posted - 2002-07-30 : 20:23:37

Sorry, for the confusion.

Both *tblSL* and *tblMGL* are both used as lookup tables...

** tblSL.mgLinkID = tblMGL.mgLinkID **

tblMGL(mgLinkID, mgID, mID)

tblB(mID, mBrandName)
tblMG(mgID, mgName)

tblSL(sLinkID, mgLinkID, sID, rID, ptID, csID, bID, eID)

tblS(sID, sDescription, sKeywords, sName, sFactor)

tblR(rID, rNumber)
tblPT(ptID, ptName)
tblCS(csID, csName)
tblBS(bID, bName)
tblCES(eID, eName)

Given these tables find all matches...

WHERE (tblS.sDescription LIKE '%" & varSearchTerm & "%' OR tblS.sKeywords LIKE '%" & varSearchTerm & "%') AND tblB.mID <> 3 ORDER BY tblB.mID, tblS.sName ASC"

BTW, before when I tried unnesting I would get an error.

Thanks.

Edited by - cyberdog on 07/30/2002 20:33:59

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources