We help IT Professionals succeed at work.

help for a pageiolatch

falkor
falkor asked
on
When running an append or make table query in access from a sql7 table to a local access table, the status immediately goes to pageiolatch.  Please do not refer me to sql books on line, or microsoft develop network, I've been there.

The table I'm querying is not normalized, it is just a flat file uploaded to the server.

Other queries against the same table do not go to this status.

Things we've tried:
1) Turned on DTC coordinator
2) Changed minimum amt. of server memory
3) Increased temp db size (alter database)
4) Increased worker threads to 512
5) Set max async_io to 128
6) Turned on sql boost

This is the select from the make table query:
SELECT sub.subid, clm_hdr2001.icn, clm_hdr2001.icn AS icn1, clm_line2001.fdos, clm_line2001.ldos, clm_hdr2001.svcpln, clm_hdr2001.clmcl, clm_line2001.groupno, clm_line2001.sect, clm_line2001.pvpar, clm_line2001.pvnum, clm_hdr2001.provtaxid, clm_line2001.pos, clm_line2001.tos, clm_line2001.proccd, LTrim([clm_line2001].[diag]) AS diag, clm_line2001.svunits, clm_line2001.prorate AS prorate, clm_line2001.pvchrg, clm_line2001.pvtype, clm_line2001.pvspec, clm_hdr2001.srcinp, clm_line2001.diagcls, clm_hdr2001.totchg, clm_line2001.deduct, clm_line2001.coin, clm_line2001.sanct, clm_line2001.copay, clm_line2001.noncov, clm_hdr2001.oiind, clm_line2001.netid, clm_hdr2001.ckdate, clm_line2001.lnum, clm_hdr2001.actpln, clm_line2001.altcoins, clm_line2001.pvzip, clm_line2001.sancind1, clm_hdr2001.sccf, clm_line2001.ldisp, clm_hdr2001.sexrel, clm_hdr2001.cptdob, clm_hdr2001.pfname, clm_hdr2001.plname, clm_hdr2001.accessfee AS accessfee, [prorate]+[accessfee] AS Totpaid, clm_line2001.pkg, clm_hdr2001.surgcd1, clm_line2001.cntycd, clm_line2001.cobsavind, clm_line2001.cobsavamt, clm_hdr2001.grpset
FROM (sub INNER JOIN clm_hdr2001 ON sub.sub_num = clm_hdr2001.sub_num) INNER JOIN clm_line2001 ON clm_hdr2001.clm_num = clm_line2001.clm_num
WHERE (((clm_line2001.groupno)='000EPC303') AND ((clm_hdr2001.ckdate) Between '08/01/2001' And '09/30/2001') AND ((clm_line2001.ldisp) Like '4%'));
Comment
Watch Question

Access isn't very nice to linked tables when several joins are involved.  Are you joining to any local acces tables? If so see if there is a way to keep or get that data on the server.  Then build a view in SQL server and link to that in access instead of the table.  In other words, let SQL server process all the joins and return the data back to Access.  The SQL Server query optimiser is much more effecient than Access's.  After that, your latching problem should go away . . .

Author

Commented:
All of the tables are on the server.  The query's where clause is limited by criteria from the access application.  Is it normal in this case to call a SP which would create a dynamic view based on what it was passed from access, or to create a view with all of the joins, and then query the view through access with the criteria?
Yes, the creating the view, linkng to it, and then parameterizing the where clause of an Access query with the view in the select clause should work fine.  Alternatively you can create a stored procedure that returns records and pass parameters to it via VBA and DAO or ADO.  But the view is the easiest . . .

Author

Commented:
Well, I was a little confused as to why the query worked before, and now has an issue.  I believe the problem is hardware related.  We are in the middle of defragging the server - was 85% fragged.

Your solution will work however, if this latest development does not.  Thanks for your assistance.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.