| Author |
Topic |
|
DarkConsultant
Starting Member
17 Posts |
 Posted - 2008-03-18 : 22:22:31
|
| Hello,Before I ask a question, I am visually impaired and cannot read printed words only on a screen. I am 51 years old and have been coding professionally since I was 23 (yes that long). I have four apps on the market and run a small consultancy company in the UK and my SQL knowledge is scant. I have read two books on the subject and thought I had an idea on how SQL works.I used to belong to another forum but they gave me a hard time so please go easy on me.I developed an ADO app ages ago and remembered some of it ...I have four tables (the following is a simplified version of the real thing)MainTable (Contact data like name address etc. 23 fields)Towns (Two fields TownUIN and TownName)Counties (Two fields CountyUIN and CountyName)Countries (Two fields CountryUIN and CountryName)My database is normalised to 3NF and contains no duplicate or redundant data.My problem ...Sample dataMain:MainUIN MainName MainTown1000 Fred Bloggs 11001 Fred Smith 2Towns:TownUIN TownName1 Bradford2 LeedsSELECT MainName,MainTown FROM Main WHERE MainUIN=1000 returns'Fred Bloggs' 1 (the TownUIN)I want ...'Fred Bloggs' 'Bradford'It was suggested I useSELECT MainName,MainTown FROM Main,Towns WHERE MainUIN=1000 AND TownUIN=MainTownThis returned 'Fred Bloggs' 'Bradford' but was slow.My question is what am I doing wrong?I am using SQL Server 2005 Express with VB.Net 2005 on Vista Business and XP.I am sorry my SQL knowledge is weak please dont get angry just tell me the basic thing that I do not know. |
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-03-18 : 22:51:43
|
| Your query looks generally correct except it should be "SELECT MainName,TownName" instead of "SELECT MainName,MainTown". I'm guessing that was just a typo though.Can you define what you mean by slow performance? How much data do you have in the database? Do you have any indexes setup? You mentioned that you posted simplified info...is it possible you over-simplified and inadvertently left out some critical details? |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-03-18 : 22:54:18
|
I see no reason for your query to be slow. I would JOIN like this (but the optimizer will likely form the same plan with either of our statements):select MainName ,MainTown from Main minner join Towns t on t.TownUIN = m.MainTownwhere MainUIN=1000 How many rows in the tables?Do you have any primary key or indexes on the tables?Be One with the OptimizerTG |
|
|
|
DarkConsultant
Starting Member
17 Posts |
Posted - 2008-03-19 : 00:26:54
|
| Wow guys,OK I'll do as best as I can OK? Thanks for responding.tprupsis,No there was no typo that is how it is in my query, could that be the cause of the slow down?TG,No indexes or primary keys, MainUIN is an identity field.I have ran the database with 50 and 5500 records (1000 being the working number) and got response times <300 milliseconds for a 23 fields, single contact record data return (using a datareader) which enables the user to scroll record by record thru the database (one of the design conditions) after separating out the Town, County, Country and Status fields my query used four joins and the response time fell to up to 2 seconds, making scrolling impossible. None of my fields have primary or foreign keys but each 'extra' table is in the format e.g. <Town>UIN (identity) <Town>Name (varchar(25)).Can I ask if there is any difference in using the INNER JOIN syntax as opposed to the =?Tables - Main 23 fields - Town,County,Country and Status 2 fields. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-03-19 : 08:40:09
|
| Those table are quite small (by row count) so these quereis should be very fast. First I would isolate sql server from your application by running all your statements directly in a query window (rather than via your application). You need to know where the bottleneck is.This is outside the scope of your question but generaly, all permanent production tables should have a primary key defined. Whether you use a surragate key (like your identity column) or a logical key (a combination of columns that define uniqueness) is up to you. If you go with a surragate key then you should have a unique constraint on a logical key to prohibit dupelicate rows. An index (or foreign key) on main.maintown could also help by allowing the optimzer to use the index if it deems appropriate.You can take further action once you've determined that the slowness has been resolved or not. I am thinking that the problem will be with the application because of the small table size and simple statement.Be One with the OptimizerTG |
|
|
|
DarkConsultant
Starting Member
17 Posts |
Posted - 2008-03-19 : 11:51:52
|
| TG,Thanks again for the pointers but is there any advantage to using your 'JOIN TO' syntax as opposed to the '=' syntax?I have found a bug in my application which could be the cause of the slow down, will test tonight and let you know.You are restoring my faith in human nature TG, thanks. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2008-03-19 : 12:14:16
|
| The biggest benefit is in how the SQL Server query optimizer works. MS has spent considerable time making joins using the JOIN syntax very fast. You can find more on joins at http://www.w3schools.com/sql/sql_join.asp. (Not sure how well that will work for you).I'd spend some time learning about joins. We don't have a great resource on this since it's not specific to SQL Server. The link here should help though.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
DarkConsultant
Starting Member
17 Posts |
Posted - 2008-03-19 : 14:29:51
|
| Thanks Graz,No there is nothing you can do to make this site better or more accessible.Most important - the natives are friendly ...CheersThe SQL query has been solved by the good fellows here. The problem was not SQL but my bungling attempt to attach a datasource to a textbox's AutoSuggest collection. I now return an array and addrange; sweet. My response time is now BELOW where it was before the problem.Can you all hear the champagne corks a popping ... ?Thanks again to all contributers and rest assured you WILL hear from me again, probably sooner than later. |
|
|
|
|
Retrieve Data