Access Querys to SQL (1 Viewer)

[ECEK]

Could somebody please point me in the right direction?

I am converting my Access database to SQL and trying to replicate my querys.

In one instance I have eight querys that rely on one another.
Query within a Query

Two questions:
1. Am I to assume that I would have to create nested SQL to replicate my eight querys?

Alternatively:
2.If I connect to the SQL server and replicate my querys but use passthrough querys within passthrough will I bypass the jet engine?

We are trying to increase the speed of our database by dumping all of the tables into SQL then accessing them by utilising our existing Access Front Ends but utilising the speed of the SQL and not the Jet Engine.

Thanks for your time

 

[Minty]

Be aware that moving to SQL Server is not a guarantee of massive performance increases.

In many instances you will spend a considerable amount of time tweaking your app to get the benefits of the move.

If you have complex nested queries you will get a much bigger speed improvement by utilising a SQL view, but be warned that nesting Views is pretty frowned upon in the SQL community. What you can use more efficiently are either CTE's (Common Table Expressions) or a sub query (sometimes called a derived table in SQL).

CTE's are a little harder to get your head around, but much easier to read and debug than nested derived tables. Google for examples. Same with Views.

 

[ECEK]

Thanks Minty.
I have just discovered "Views" which I shall experiment with.

I think that the problem is that our network is incapable of processing data efficiently and the provider is telling us that it's an Access problem.

Ten querys
Five Tables
Table 1 = 100k records
Table 2 = 200k records
Table 3 = 250K records
Table 4 = 20K records
Table 5 = 13 records (yes 13)

My Query Takes 1 minute 24 to appear !!!!

 

[plog]

I think that the problem is that our network is incapable of processing data efficiently and the provider is telling us that it's an Access problem.

If only some one would invent a method to scientifically test hypothesis. You don't need 3 pieces of poster board and magic markers to resolve this. Put a copy of the database on your computer and see how fast your query runs in that environment. Problem isolated.

Then, if you haven't already done so, turn to google to "optimize queries". The broad strokes are--use indexes, use the proper data types for fields, query only the data you need.

 

[pbaldy]

2-Yes, using a pass through query will bypass the Jet/Ace engine. I'd also throw temp tables in a stored procedure out there. I'd also be curious what the 8 queries do, and whether it could be done more efficiently with joins, etc.

 

[ECEK]

Thanks PBaldy and Minty (did you see who I left out?)

 

[plog]

Errgg you found my week spot---my fragile narcissim and need for internet validation.

Day ruined.

 

[Mark_]

Errgg you found my week spot---my fragile narcissim and need for internet validation.

Day ruined.

Sorry, we've a fair number of those around here.

Not really a "Week" spot unless your mixing data types; then you should use a Date/Time.

 

[NauticalGent]

... but be warned that nesting Views is pretty frowned upon in the SQL community

Why is that? I am relatively new to the SQL (under) world and I was always led to beleive that Views were the answer to all our woes - Server-side processing and all the jazz...

Just when I think I know something...

 

[Minty]

I think the reason is two-fold - firstly apparently they don't (didn't used to) make best use of the execution plans available, and secondly from a design perspective any changes you make to any of the underlying views will invariably break the following dependent views. As it's more common for multiple developers to be working in a single database I guess this is more of a problem than in a single dev environment.

I personally do use a couple of views to filter specific subsets of records that I then base a whole raft of other views on, specifically because I need all the dependant views to have the same basic subset of records, and also need to report on what is missed from that reporting.

Using one view to control the included PK ID's for me is a really straight forward way of controlling that, knowing that if the base line criteria change it will automatically feed through to all the child reporting.

A little more here https://www.red-gate.com/simple-talk/sql/performance/the-seven-sins-against-tsql-performance/#seven

 

[NauticalGent]

That link was a little over my head but I did understand enough to get the gist. Thanks for the post!