SQL Server Distributed Queries!!!

Distributed queries in SQL Server are a blessing and a curse. What can I say, they are fascinating. For those of you who are not familiar with distributed queries, here is the crash course.

What is a distributed query?

So here is the deal, a distributed query is query that relies on data that lives on another data source. The classic example of distributed queries is the Linked Servers in SQL Server. You can also use the OpenQuery and OpenRowset stored procedures to get to the remote data. The difference between OpenQuery and OpenRowset is that the OpenQuery uses linked servers (linked servers store the connection string data); whereas OpenRowset requires the entire connection string to be passed.

For sanity purposes I like the OpenQuery it is cleaner and is more abstract.

Example:

What not to do!

SELECT RTRIM(name), id FROM OPENQUERY(linkedserver, ‘SELECT name,id FROM foo’) WHERE id = 24234

–Much better!

SELECT name, id FROM OPENQUERY(linkedserver, ‘SELECT RTRIM(name),id FROM foo WHERE id = 24234’)

In the above example something might be popping in your head, and you’re right, it is a nested query! The theory that I use often is this: All you are doing is creating a temporary table from one server to use on another. All of the processing inside the OpenQuery occurs on the linked server first and then fills in a temporary table on the SQL Server from whence you are executing the distributed query. SQL Server then takes that temporary table (results of the OpenQuery) and runs the remainder of the query.

Here is the order of execution, prepare to have your mind blown:

  1. OPENQUERY
  2. FROM
  3. WHERE
  4. SELECT

Here is the mind blowing part, this also relates to performance. Let me explain. The more criteria (restricting the number of rows and fields) you impose in step 1, makes all the remaining steps faster. Likewise any data manipulation you do in step 1 will speed up the entire query. If you have more than one OPENQUERY linking to eachother then join them if possible (they have to be from the same data source), see the example below. Try to get to one OPENQUERY. Remember what I said from earlier, it downloads all that data and puts it into temporary tables. You could take 5 Mb of data transferred from a couple OPENQUERY’s and combine them to be under 1 Mb, this has a huge difference in network traffic and computing time on the SQL Server.

–This is really bad.

SELECT * FROM OPENQUERY(linkedserver1,’SELECT name, id from foo’) AS foo INNER JOIN OPENQUERY(linkedserver1,’SELECT name2, id from goo’) AS goo

ON foo.id = goo.id

–This is much better.

SELECT * FROM OPENQUERY(linkedserver1,’SELECT name, name2 FROM foo INNER JOIN goo ON foo.id = goo.id’)

Gotcha’s with Eloquence and the SQL/R ODBC

Only inner joins are accepted in the very old style: FROM foo, goo WHERE foo.id = goo.id

You can only link 5 tables per OPENQUERY

If you have any questions, just post a comment.

Advertisement

Visual Studio 2005 Tip of the Day – Class Diagram

I just have a really brief entry for today. In my continued efforts of self-improvement, I have stumbled upon a rather interesting feature in VS that I never knew (or bothered to look at). For some developers, they need to visualize what they are doing in one way or another. Well the good people at Microsoft have developed a GUI for creating classes called Class Diagram.

Right mouse click on your solution in the Solution Explorer and select Class Diagram. In there you will discover that you will be able to create the shells of your classes very simply and quickly. You can add methods, background fields, properties to new or existing classes you have created. In each of these you can include parameters and comments.

Once you include some code in the classes, you can even test the results with Test Bench. Basically you can enter values into the parameters of functions and see the output, without even writing the old main function and running through test case after test case. You still might want to do that anyway, so you can repeat your tests.

Well that is it for now!

Things I have learned today: Word and Access Tricks!

Business meeting I have come across some things today that I need to share, because I have not found them many places! My main purpose on here is to be helpful to others as well as maintain a digital image of my brain; simply because, like many people in IT, I have to address many different things in any given day.

So on with the tricks!

Access:

Turning Off Stubborn Warning Messages

Today I was helping a colleague with a macro they were creating. The person turned the warnings off and were still getting the warning message about appending records. Here is how you get around warning messages that will not turn of the default way.  Use the SendKeys Action immediately BEFORE the append query (or whichever action is generating the warning) you need to hide. Be sure to set it’s “Wait” attribute to “Off”. The enter key is represented as a tilde “~”, which will automatically post on the message box. If you need to tab to the the next button, you can use {TAB}.

Open Access Database on a Network

Another thing I learned today is that the Window XP will not allow you to open an Access database file when it is on a network (non-Active Directory anyways). Also the file must not be open by anyone else.

Word:

Dynamically Loading Images Into Mail Merge

If you are doing a mail merge and need an image to change on each record here are some tips. Paths have to include an extra backslash so \’s are now \\. The format is {INCLUDEPICTURE {MERGEFIELD ImagePathNameHere}}. Once you generate the output to a different file, select all (Ctrl + A) and then press F9 to have all the pictures load. If you this incorrectly one of two things happen: you see the same image for all the records; if you see this, chances are you did not select all the records and refresh them. If all the images appear like broken links, you did not include the extra backslashes in the file path.

If you have any questions about this or anything else, I will do my best to answer you. Just leave me a comment!