SQL Server backend - Very SLOW Query/Form

[litsz02]

I've done alot of research and I'm not coming up with very much. I have upsized a Access 2000 database to SQL Server, so I can use the Access application as a front end.

I notice some serious performance degrade. I have a rather lengthy query (I've attached below one of the slower ones - it is the SQL View of the Query if interested) that I have on a ACCESS 2000 Front end going against SQL server backend table. I know that through the MS Jet engine, by default it would run locally as I understand it.

Does anyone know the best way to force the query to run on the server? I understood through research that some expressions (such as Where, Having, etc. are not supported to run on the server side?) I'm just noticing that before upsizing the the database queries ran about 20 seconds faster. I'm having trouble now convincing the client this was a good move. I have searched the site and done most recommendations so I'm not too sure what to do. Any suggestions would be appreciated!

-----------------------------------------------------
SELECT [Insertion Orders].[Publication Name],
[Insertion Orders -- Line Items].[Invoice#], Publications.District,
Sum([Insertion Orders -- Line Items].[Ad Rate]) AS [SumOfAd Rate],
[Media Plans].[Cost Center],
[Media Plans].[MITS Code],
[Insertion Orders].[LOB Code],
[Publications -- Types].[Subclass Code],
[Insertion Orders].[Bill To:], [Insertion Orders].[Media Code]
FROM ([Publications -- Types]
RIGHT JOIN Publications
ON [Publications -- Types].[Newspaper Type] = Publications.[Publication Type])
INNER JOIN ([Media Plans]
INNER JOIN ([Insertion Orders]
INNER JOIN [Insertion Orders -- Line Items]
ON [Insertion Orders].[Order#] = [Insertion Orders -- Line Items].[Order#])
ON [Media Plans].[Media Code] = [Insertion Orders].[Media Code])
ON Publications.[Publication Name] = [Insertion Orders].[Publication Name]
GROUP BY [Insertion Orders].[Publication Name],
[Insertion Orders -- Line Items].[Invoice#], Publications.District,
[Media Plans].[Cost Center],
[Media Plans].[MITS Code],
[Insertion Orders].[LOB Code],
[Publications -- Types].[Subclass Code],
[Insertion Orders].[Bill To:], [Insertion Orders].[Media Code]
HAVING
((([Insertion Orders -- Line Items].[Invoice#]) Is Not Null) AND ((Mid([Insertion Orders].[Media Code],4,1))=Mid([FORMS]![begin].[InsertionYear],4,1)));

 

[lespaul]

I don't have any experience with this personally, but I've seen postings that indicate if you change this to a "Pass-Through" query it will run on the server side and should be faster.

HTH

Leslie


Leslie

 

[litsz02]

Thanks - That seems like a best fit I think too. I thought about doing that but wasnt sure if that was best practice or what.

The only trouble I'm having now is a syntax error with my last statement:
((([Insertion Orders -- Line Items].[Invoice#]) Is Not Null) AND ((Mid([Insertion Orders].[Media Code],4,1))=Mid([FORMS]![begin].[InsertionYear],4,1)));

I had changed it to read this because the Mid() function is not supported by SQL server.:
((([Insertion Orders -- Line Items].[Invoice#]) Is Not Null) AND ((SUBSTRING([Insertion Orders].[Media Code],4,1))=SUBSTRING([FORMS]![begin].[InsertionYear],4,1)));

This should work but I'm getting an error about the "!" saying there is a syntax error.

Does the substring function not support "[FORMS]!" syntax? I have to get the middle part of the data in order to compare, which is why I'm using substring.

 

[litsz02]

I found a resolution to this. I created a table with a 2 columns - 1 Autonumber and another the Year field I am validating against on SQL server. I just update where the records has autonumber of 1 and use it as a temporary variable. You obviously cannot bring over form data variables to validate against when using SQL server's pass thru queries. Thanks for your input

 

[lespaul]

glad I at least pointed you in the right direction!

leslie

 

[cmmrfrds]

You can send literals to sql server and there is no reason not to in this case. Resolve the Form control name to a literal before sending.

=SUBSTRING([FORMS]![begin].[InsertionYear],4,1)));

Assuming that you are making the sql statement a string and starting from the =.
"='" & Mid([FORMS]![begin].[InsertionYear],4,1) & "'));"

 

[PHV]

As you don't use aggregate in your HAVING clause, do all the test in a WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[litsz02]

I have a very slow query left still:

SELECT [Insertion Orders].[Publication Name],
[Insertion Orders -- Line Items].[Invoice#],
Publications.District,
Sum([Insertion Orders -- Line Items].[Ad Rate])
AS [SumOfAd Rate],
[Media Plans].[Cost Center],
[Media Plans].[MITS Code],
[Insertion Orders].[LOB Code],
[Publications -- Types].[Subclass Code],
[Insertion Orders].[Bill To:]
FROM YearData
INNER JOIN (([Publications -- Types]
RIGHT JOIN Publications ON [Publications -- Types].[Newspaper Type] = Publications.[Publication Type])
INNER JOIN ([Media Plans]
INNER JOIN ([Insertion Orders]
INNER JOIN [Insertion Orders -- Line Items]
ON [Insertion Orders].[Order#] = [Insertion Orders -- Line Items].[Order#])
ON [Media Plans].[Media Code] = [Insertion Orders].[Media Code])
ON Publications.[Publication Name] = [Insertion Orders].[Publication Name])
ON YearData.InsertionYear = [Insertion Orders].YEARINDEX
GROUP BY [Insertion Orders].[Publication Name],
[Insertion Orders -- Line Items].[Invoice#], Publications.District,
[Media Plans].[Cost Center],
[Media Plans].[MITS Code],
[Insertion Orders].[LOB Code],
[Publications -- Types].[Subclass Code],
[Insertion Orders].[Bill To:]
HAVING ((([Insertion Orders -- Line Items].[Invoice#]) Is Not Null));


I know that with a SQL Backend and Access front end it seems to do some local SQL criteria, (where clauses, etc.) i defined this as a pass thru query and it still takes forever. Is there a clause in here that i can change that would increase performance? thanks for your help.

 

[PHV]

Again, you don't need a HAVING clause but a WHERE one.
Is the RIGHT JOIN really needed ?
Have you tried PassThru query ?
If you play with linked tables, are all the index properly recognized by Access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

Did you follow the advice of phv? Why are you checking for null in the having clause which is done after a full table scan on the table - this will cause a bypass of any indexes that could be used. Put the check in the where clause.

ON YearData.InsertionYear = [Insertion Orders].YEARINDEX
WHERE [Insertion Orders -- Line Items].[Invoice#] Is Not Null
GROUP BY [Insertion Orders].[Publication Name],
[Insertion Orders -- Line Items].[Invoice#], Publications.District,
[Media Plans].[Cost Center],
[Media Plans].[MITS Code],
[Insertion Orders].[LOB Code],
[Publications -- Types].[Subclass Code],
[Insertion Orders].[Bill To:]
;

Instead of checking for null it is better to check for a criteria
WHERE [Insertion Orders -- Line Items].[Invoice#] > 0
for example.

 

[litsz02]

Great I'll try it Thanks so much....
That is just the SQL generated from the Query View in Access 2000. I guess the right join is necessary because of the way the tables are shown on the map in the query. I can rearrange it and play with it though.

See i had read somewhere that when using SQL server that Where clauses actually Decrease performance because SQL Server caches info and then performs the "WHERE" clause locally on the data. A having clause I thought was actually done on the server side?

I did try a pass thru query though. It didnt speed anything up for me to my surprise.