SQL : SELECT statement (Question - Newbie)

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

Transact-SQL (2000)

SQL : SELECT statement (Question - Newbie)

Author

Topic

beldeamon
Starting Member

4 Posts

Posted - 2004-03-29 : 10:44:00

Hi,

Am trying to pull data out of 2 tables, using a third which links to
2 via a key. I am very new to SQL so bare with me ;-)

I am playing with an extract of a free music database i found on the
web and am building a little front end to this data. My problem is as follows.

I need to merge data from two tables (the artist table and the album
table) using a third table, which only has two columns (a key from artist and a key from album). My initial stab at the SQL looks as follows :

SELECT A.ARTIST, B.ALBUM
FROM ARTIST A, ALBUMS B, ALBUMSX C
WHERE A.ARTIST LIKE '%xxxx%'
AND A.ARTKEY=C.ARTKEY AND B.ALBKEY=C.ALBKEY

As A has about 30,000 records, B 120,000 and C 120,000 i presume as
my query seems to be off running in some black hole, i havent got this one as good as it could be. (Short term I have no indexes and the keys are 32 bytes ... I know dont ask!!..lol lol )

I presume i need to nest the query somehow ... Anyone kind enough to
give a stab at some pointers.

Thanks in advance

Stuart - France

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-29 : 11:04:33

Create an index on your ARTKEY, ALBKEY, and ARTIST. Format the query like this:

SELECT
A.ARTIST,
B.ALBUM
FROM
ARTIST A
INNER JOIN ALBUMSX C ON A.ARTKEY = C.ARTKEY
INNER JOIN ALBUMS B ON C.ALBKEY = C.ALBKEY
WHERE
A.ARTIST LIKE '%xxxx%'

Do you really have to do the LIKE '%xxxx%' If you have a wildcard on the beginning it will not use the indexes. If you at least enforce 'xxx%' it would run much faster because you could use an index.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

beldeamon
Starting Member

4 Posts

Posted - 2004-03-29 : 11:19:42

Derrick,

Thanks for coming back so quick :-)

I will have a try with your suggestion and also create the required
index's which i knew were lacking.

As this is for personal use and an introduction for me into SQL (am using Delphi under windows and SQLITE for the DB), your point on the
wildcart search is valid, the LIKE statement doesnt really need to be
so %wild% as all that.

Thank you again for your time and help .... appreciated.

Will report back on this thread with my findings.

Regards

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-29 : 11:37:58

Given the %XXXXX% the index on the "artist" column will be useless for this query....the problem is with the leading %...the program will have to read every record (table-scan) regardless of the index.

beldeamon
Starting Member

4 Posts

Posted - 2004-03-29 : 12:03:04

Derrick,

Success :-) Query now runs in about 4 secs here, which is not bad
considering the DB is now some 65mb (Probably all in memory)

This is more than quick enough for my needs, and seems pretty performant for the amount of data.

Andrew, I understand i think that the wildcard leading % invalidates
index and forces full-read,its noted and will use spareingly, but at
4 secs for a fullwild, its not that bad at all.

Thanks again

Building table from external file ... Running SQL
SQL RC =0 for copy artist from 'rdf.aar' using delimiters '^'
Creating Index ... Running SQL
SQL RC =0 for create index inartist1 on artist(artist)
Creating Index ... Running SQL
SQL RC =0 for create index inartist2 on artist(artkey)
Building table from external file ... Running SQL
SQL RC =0 for copy albums from 'rdf.aal' using delimiters '^'
Creating Index ... Running SQL
SQL RC =0 for create index inalbums1 on albums(album)
Creating Index ... Running SQL
SQL RC =0 for create index inalbums2 on albums(albkey)
Building table from external file ... Running SQL
SQL RC =0 for copy albumsx from 'rdf.xal' using delimiters '^'
Creating Index ... Running SQL
SQL RC =0 for create index inalbumsx1 on albumsx(artkey)
Creating Index ... Running SQL
SQL RC =0 for create index inalbumsx2 on albumsx(albkey)
Rowcount is 127 returned from Select a.artist ,b.album FROM artist a Inner Join Albumsx C on A.ARTKEY=C.ARTKEY Inner Join Albums B on B.ALBKEY=C.ALBKEY where a.artist like '%beatles%' order by 1 : Elapsed 4.598 secs

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-29 : 12:31:11

That's because it's a scan...

Take the leading % out, then let us know...

It'll probaly tell you that it took 0 seconds...

Brett

8-)

beldeamon
Starting Member

4 Posts

Posted - 2004-03-29 : 12:56:26

Brett et al,

Query on speed ..lol, its sub 1 second for sure, but cant say exactly as I have somehow now screwed my windows performance
counters, well at least that i can sort all on my lonesome ;-)

Greetz from France

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources