June 1, 2006 at 4:38 pm
Hi... I’m in the process of migrating an MS ACCESS database to sql 2000. DTS did not work because of some virtual table links(access) that are to be views of tables from a linked server in my sql 2000 environment.
The design is poor and I will have to rewrite the queries to clean things up but I am new to this industry so we decided to go ahead and get it running then document what the business analyst was trying to do with all these Access queries over queries over queries.
Just for background, an MS Access query translates to a view in sql 2000. so I am constructing views upon view upon view to recreate this horrid design. I don’t mind, however, because I am learning how the analyst approaches this new field I am in. cool.
the final interesting aspect of these nested views (four deep) is the base table joins on the deepest level is a view of a table from a linked server. Hope that makes sense.
So here is the weird behavior I am seeing. After building a view with three nested views within, that view would time out when issuing a select * . However, when I run the SQL the view is created from, the result set returns in seconds. Any ideas?
I should also say that in order to create the views on the linked server I had to issue the following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Just before the create view statement. Can anyone tell me about that?
SQL 2012 Standard VPS Windows 2012 Server Standard
June 2, 2006 at 6:46 am
Wow. So many comments come to mind.
First of all, nested queries do not necessarily imply poor design. However, it is entirely possible that you do have poor design, and the nested queries may be symptomatic.
It sounds to me like you're actually going to want to only bring the tables over via DTS, and then recreate each view manually. First, it's the best way to make sure you cover referencing linked tables. Second, it will be the best way to gain insight into the thought processes.
As for your timeout issue, I'm going to guess that you have some issues with poorly optimized queries. There are lots and lots of ways to poorly optimize queries. If the straight SQL is running quick, but the view isn't, you're probably kicking off multiple full-table scans, and the query plan is screwed.
I strongly recommend that you go through the Books On Line (referred to as BOL here). Look into ways to optimize your queries. You can also find the information about those SET statements there.
June 2, 2006 at 8:12 pm
Your absolutely right. I did transfer over the base tables DTS-style and am recreating the queries as views, gaining valuable insight to the design and subject matter. And I will hit the BOL for sure.
But if I can reiterate what your saying. The query a view is based on will execute with a different plan than the view itself? Not intuitive but ther is one sure way to find out! I'll take a look on Monday. Its the weekend now!
Thanks!
SQL 2012 Standard VPS Windows 2012 Server Standard
June 5, 2006 at 5:12 am
Sometimes yes, and sometimes no. It depends a great deal on how complicated the query is. The query optimizer is a strange little beast, that seems to operate entirely on fuzzy logic. Sometimes a one-shot query will just hit on a great execution plan, while the view may be stuck trying to optimize based on outdated statistics, and end up doing a number of full table scans.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply