Sql behind query builder question 4

[vacant]

Frequently, at work, I will be in a situation where I import a table of data concerning a small number of patients (typically <1000 records), and where the only field in this table that refers to the patient is his/her NHS number.
The next stage of the job is then to use this NHS number to query the main database (est. 3 million+ records) to obtain their names, dates of birth etc.

One thing I have noticed is that, after building my query in design view, if I peek at the SQL then it differs according to how I ‘drew’ the relationships.
For instance if I drag the NHS number from the main_database table onto the patient_data table I get the following:

SELECT patient_data.[nhs number], main_database.name, main_database.address, main_database.dob
FROM main_database INNER JOIN patient_data ON main_database.[nhs number] = patient_data.[nhs number];

Whereas if I make the relationship in the reverse direction I get this:

SELECT patient_data.[nhs number], main_database.name, main_database.address, main_database.dob
FROM patient_data INNER JOIN main_database ON patient_data.[nhs number] = main_database.[nhs number];

Now I know nothing about SQL, but I can see they are different. Does it make any difference to the way the computer carries the process out?
You see, if I was doing it physically myself (God forbid), it would seem the best way would be to take a record from the patient_data table and then look for the matching one in the main_database table, rather than the other way. Ok I’ll shut up now, over to you…

 

[Falcon99]

I wouldn't worry, trust Access on this one.

I'm no SQL expert but I would not expect any difference as long the the joins are inner joins.

 

[vacant]

tee hee
well at least it wasn't a one word answer

i was just wondering because these queries can be tediously slow. not that i really mind the sitting around but the boss is starting to notice

 

[cmmrfrds]

Are you linking access to a remote database table? Quantify slow? If an external database, what vendor/name?

 

[Falcon99]

Your using Access that has a table of 3 million+ records?

I'm sorry but I believe it's time for some serious upgrading considerations.

 

[JeremyNYC]

No, there is no difference between those two queries. You might try using an inner join, though:
SELECT patient_data.[nhs number], main_database.name, main_database.address, main_database.dob
FROM main_database RIGHT JOIN patient_data ON main_database.[nhs number] = patient_data.[nhs number];

By changing INNER to RIGHT you tell it to start with just the records that exist in Patient_Data (which I'm assuming is the smaller of the two tables), as opposed to having to look through every record of both tables. The query optimizer may be smart enough to search this way anyway, but I would try it both ways, timing each one. It might help.

But with 3 million records, it's gonna take a while. Make sure your indexes are helping, not hurting. Here's a site with a zillion ideas for how to speed up Access databses:

http://www.fmsinc.com/tpapers/faster/index.html

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[vacant]

give that man a star

i'll give the RIGHT JOIN idea a go, that does sound the logical way to search if that's how it works. cheers Jeremy

cmmrfrds and falcon99, the database I’m querying lives in a box about 6 foot high, and I link to it somehow through a server running something called m-connect. All I know is that it appears as a regular linked table in my access database

 

[tlbroadbent]

If the large table is a linked table on remote database, you are not going to get very fast performance. Access often has to pull back all 3M rows and perform the selection on the client. It would be helpful if the large table could be filtered at the remote server.

Sometimes you can boost performance when querying remote databases by using pass-through queries if they are supported by the remote database. You can select only the columns you need and even perform some filtering at the remote server. This cuts down the network traffic and reduces the load on both the server and the client.

I have to disagree with Jeremy's explanation of the RIGHT JOIN. Using an OUTER JOIN instead of an INNER JOIN simply means that all rows in patient_data will be selected along with matching data from main_database. It does not cause Access to start with the patient_data table. In reality, the Access Jet Engine will probably execute the query in much the same way whether using an INNER or OUTER JOIN. In my experience OUTER JOINs are often slower than corresponding INNER JOINs. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[JeremyNYC]

I certainly won't question Terry's correction of my OUTER, INNER statement. I guess it was sort of wishful thinking. The applications that I work on tend to be a lot more code intensive than data intensive, so I've never had to get too deep into timing different Sql constructs. Thanks for the correction, Terry.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[vacant]

Wow I’m chucking around stars like Bruce Lee here. Cheers fellas

Armed with the knowledge imparted above, I felt confident enough to broach the subject with the hardware boys today. Their feelings were that the m-connect software on the server my query gets sent through will interpret whatever SQL I throw at it in its own way, and that most of the processing is done there.
Seemingly then I’ve been kinda wasting your time, and that I’m destined to spend half my working life sat staring at a blank screen, never quite knowing when it's gonna return any results, or whether it crashed an hour ago
Maybe I’ll take up knitting

 

[cmmrfrds]

It seems the hardware boys don't want to be bothered. If you are using a linked table the largest part of the time is spent sending the data over the network back to Access to be joined with the Access table. Most of the processing will be done on the server if you use a PASS-THROUGH query, but not in the case of a linked table. The best solution is to somehow structure so that you can use a pass-through query.

Is this query the record source for a Form or Report? If not, there may be some ways through vba code to set the query up as a pass-through.

Ask the hardware boys, if m-connect has an OLE-DB Provider. If so, you can use ADO which may allow additional options.

 

[vacant]

OLE-DB? ADO? I can see the blank looks now...
In answer to your question, no the queries are not the basis of any forms or reports. I simply design each one according to the extra patient detail I require at the time, and then export the results as a text file or whatever.
Just to clarify the situation at present: in Access97 I choose GetExternalData then LinkTable then ODBC Source, and I then choose the server that's running m-connect. This server, I understand, is connected via a Unix network to the central database. The m-connect software represents the central data as a bunch of tables and I simply click on the ones I know contain the data I need.
Are you saying I could send my query directly to this server? Sorry if I totally misunderstand. I've never heard of a PassThrough query so I'm not gonna expect you to guide me through the entire process, have you got any links I can point at?

I really appreciate all this help everyone

 

[JeremyNYC]

Terry,

Rather than expecting someone else to guide you through a process that you want to learn, do some research yourself. No one is asking you to learn PassThrough queries, someone is merely suggesting that they couldhelp in your situation. If you want to improve your database, you can do some research. I'm sure that if you came here with specific questions about PT queries plenty of people would help you. But in general, it's a bad idea to expect other people to do your work for you.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[cmmrfrds]

A couple of links to get you started.

http://www.songhaysystem.com/document.php?cmd=getDoc&get=1

http://support.microsoft.com/default.aspx?scid=KB;en-us;q112108

Try to structure your query such that all the work can be done on the server - pass-through query.

Here are a couple of thoughts.
1. Make the local table a list in your query and send the query with the list to the server. There are different ways to make the list but here is the general idea.

SELECT main_database.[nhs number], main_database.name, main_database.address, main_database.dob
FROM main_database
Where
main_database.[nhs number] IN (123, 456, 789, etc..)
'- the list contains the ids from the local table.

2. Make 2nd pass-through query to insert the local table keys on the server. You would need create permission on the server. First, send to the server a Create Table query. Next, insert all the ids in the that table on the server. Now, you can join the inserted table to your main table to run the query. Finally, do a drop table query.

Both of these scenarios would put all the processing on the server.

 

[vacant]

thanks again cmmrfrds, that's more than enough to get me started.
right, i'll go off and study the topic now, and check back here with any specific questions i may have
jeremy, if you re-read my ramble, i actually said i DIDN'T expect to be guided through the process

thanks to you all one more time

 

[JeremyNYC]

Terry,

Wow. A VERY sincere appology. I don't know what happened. I read that three times to be sure of what I was seeing. That's very weird.

In any case, it looks like you got some good tips. Best of luck.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[vacant]

Apology accepted Jeremy. I often blame it on the brandy in the mince pies at this time of year

Yeah I received some great advice thanks, and I look forward to learning some more from you all in the new year.
Have a good one folks…

 

[Terpsfan]

One thing that you might also want to look at is index optimization. As in, make sure you have indexes on the fields you are joining as well as fields in your where statement. That in itself can speed up queries. You could also try importing the table from SQL Server, Oracle, or whatever remote DB you are using from within Access instead of using a linked table.