[dba-SQLServer] SPAM: Re: sql performance monitoring

[David Emerson]

I have a related question which may add to the options.  I have a report that has multiple sub reports (up to 40)  I am currently using Passthrough queries which get data from stored procedures.  We are hitting Out Of Memory errors.

If I convert the stored procedures to save the records to SQL tables, link the tables to my Access front end, and use the tables for my reports, will this take up less memory than getting the records via a passthrough query?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Wednesday, 25 April 2018 5:48 a.m.
To: Discussion concerning MS SQL Server
Subject: SPAM: Re: [dba-SQLServer] sql performance monitoring

As Scott said, the first thing to look at is your queries. First, find all
data- and row-sources that begin with "SELECT" and replace those with named
queries. Next, convert all these named queries into SQL Views. (The reason
for this is that when Access uses a SELECT ... WHERE it first requests all
the data, then filters it on the local machine. When Access uses a SQL
View, the data filtering occurs on the server, not the local app. With a
lot of users, the difference can be dramatic.

If your forms contain tabs, then use Colby''s JIT form-style to postpone
the data-loads until they are needed.

Another technique, especially good for frequently-sed combo- and
list-boxes, is to declare a global variable for each list and populate them
all at once. That way they live in RAM and no round trips are required.
That can result in significant savings, too.

Finally, you could convert some of your queries to Pass-Through-Queries.
They aren't difficult, but if you haven't used them, you may have to do
some reading up on them first.
?
Arthur
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com