We have lots of Access tables which are linked to a SQL Server backend. Some of the queries are very slow and I believe the cause is row-by-agonizing-row Selects.
Can anyone point me to an online article that explains why RBAR occurs in this scenario?
Would using Pass-Thru queries eliminate the issue?
Thanks!
Being linked to access in of itself might not be the cause.
Have you identified the bottleneck queries?
It will depend on the driver you use to connect to the SQL Server tables. ODBC drivers may utilize a cursor/RBAR, or it's imposed by MS Access, if you're binding data in such a way as to allow cursor movement (table grid, or a form). OLEDB drivers might be better at this since they're newer, and were optimized for web/stateless access. SQLCLI drivers should be even more efficient. However again, since MS Access has its own interfaces, it may be forcing cursor access behind-the-scenes.
If you have queries that are doing JOINs between Access linked tables, then you are definitely getting RBAR. Access doesn't have a mechanism for pushing JOINs up to the source database (known as "query folding" in PowerBI).
Pass-through queries will avoid this problem, however you lose the ability to modify the data. If that's not an issue, then definitely look into pass-throughs.
You might also look up "DSN-less connections", these allow you to connect without having to configure an DSN in the ODBC control panel. I always found these to perform better in MS Access, and they removed a dependency.
You not given us much to go on. Querying Access across a linked server may not be the issue. What is most likely the issue is the actual code your using to query across the linked server.
My recommendation would be to post the code you're having issues with, for starters.
@JeffModen - Thanks.
This view returns 486 K rows in 14 secs.
SELECT ShipmentId, MAX(CASE WHEN RefTypeId = 1 THEN Value END) AS [Client BOL Number], MAX(CASE WHEN RefTypeId = 2 THEN Value END) AS [PO #], MAX(CASE WHEN RefTypeId = 3 THEN Value END) AS [Airway Bill #],
MAX(CASE WHEN RefTypeId = 5 THEN Value END) AS [Client ERP Sold To], MAX(CASE WHEN RefTypeId = 6 THEN Value END) AS [Client ERP Consignee Acct], MAX(CASE WHEN RefTypeId = 7 THEN Value END) AS [Trailer #],
MAX(CASE WHEN RefTypeId = 8 THEN Value END) AS [Container #], MAX(CASE WHEN RefTypeId = 9 THEN Value END) AS [Custom BOL#], MAX(CASE WHEN RefTypeId = 10 THEN Value END) AS [Sales Order Number],
MAX(CASE WHEN RefTypeId = 11 THEN Value END) AS [Sales Name], MAX(CASE WHEN RefTypeId = 12 THEN Value END) AS [Seal Number], MAX(CASE WHEN RefTypeId = 14 THEN Value END) AS [Order Type],
MAX(CASE WHEN RefTypeId = 15 THEN Value END) AS [BOL Stop Sequence], MAX(CASE WHEN RefTypeId = 16 THEN Value END) AS [Direction Code], MAX(CASE WHEN RefTypeId = 17 THEN Value END) AS [BOL Status Code],
MAX(CASE WHEN RefTypeId = 38 THEN Value END) AS [Niteo Shipper Ref 1], MAX(CASE WHEN RefTypeId = 39 THEN Value END) AS [Client Spot Quote Number], MAX(CASE WHEN RefTypeId = 40 THEN Value END)
AS [Client Spot Quote Amount], MAX(CASE WHEN RefTypeId = 41 THEN Value END) AS [Delivery #], MAX(CASE WHEN RefTypeId = 42 THEN Value END) AS [Shipment #], MAX(CASE WHEN RefTypeId = 43 THEN Value END)
AS [Flair Shipper #], MAX(CASE WHEN RefTypeId = 44 THEN Value END) AS [HOUSE BILL OF LADING #], MAX(CASE WHEN RefTypeId = 45 THEN Value END) AS [MASTER BILL OF LADING #],
MAX(CASE WHEN RefTypeId = 46 THEN Value END) AS [ORGILL PO #], MAX(CASE WHEN RefTypeId = 47 THEN Value END) AS [ORGILL GL CODE], MAX(CASE WHEN RefTypeId = 48 THEN Value END)
AS [Carrier Spot Quote Bid #], MAX(CASE WHEN RefTypeId = 49 THEN Value END) AS [EDI BOL #], MAX(CASE WHEN RefTypeId = 50 THEN Value END) AS [OB BOL #], MAX(CASE WHEN RefTypeId = 51 THEN Value END)
AS [Pick Slip #], MAX(CASE WHEN RefTypeId = 52 THEN Value END) AS [Customer Sales Order #], MAX(CASE WHEN RefTypeId = 53 THEN Value END) AS [Customer PO], MAX(CASE WHEN RefTypeId = 54 THEN Value END)
AS [INTL BOL #], MAX(CASE WHEN RefTypeId = 55 THEN Value END) AS [INTL Entry #], MAX(CASE WHEN RefTypeId = 56 THEN Value END) AS [INTL Entry Date], MAX(CASE WHEN RefTypeId = 57 THEN Value END)
AS [INTL Cube], MAX(CASE WHEN RefTypeId = 58 THEN Value END) AS [INTL # OF CONTAINERS], MAX(CASE WHEN RefTypeId = 59 THEN Value END) AS [Temp Control], MAX(CASE WHEN RefTypeId = 60 THEN Value END)
AS [INTL $ OF SALE], MAX(CASE WHEN RefTypeId = 61 THEN Value END) AS [Shipment ID #], MAX(CASE WHEN RefTypeId = 62 THEN Value END) AS [Customer Number], MAX(CASE WHEN RefTypeId = 63 THEN Value END)
AS AETC, MAX(CASE WHEN RefTypeId = 64 THEN Value END) AS [Order #], MAX(CASE WHEN RefTypeId = 65 THEN Value END) AS [Marianna Pro #], MAX(CASE WHEN RefTypeId = 66 THEN Value END)
AS [WMBARR SHIP TO CONS ID], MAX(CASE WHEN RefTypeId = 67 THEN Value END) AS [WMBARR SOLD TO MASTER ACCOUNT], MAX(CASE WHEN RefTypeId = 68 THEN Value END) AS [BOL NUMBER],
MAX(CASE WHEN RefTypeId = 69 THEN Value END) AS [LOAD #], MAX(CASE WHEN RefTypeId = 70 THEN Value END) AS [SALES $], MAX(CASE WHEN RefTypeId = 71 THEN Value END) AS ISHAZMAT,
MAX(CASE WHEN RefTypeId = 72 THEN Value END) AS [PORT OF LOADING], MAX(CASE WHEN RefTypeId = 73 THEN Value END) AS [PORT OF DISCHARGE], MAX(CASE WHEN RefTypeId = 74 THEN Value END) AS CARTONS,
MAX(CASE WHEN RefTypeId = 75 THEN Value END) AS [ACH PAID DIRECTLY], MAX(CASE WHEN RefTypeId = 76 THEN Value END) AS [DESCRIPTION OF GOODS], MAX(CASE WHEN RefTypeId = 80 THEN Value END)
AS [MODINE INTERNATIONAL MODE], MAX(CASE WHEN RefTypeId = 81 THEN Value END) AS [Packing List #], MAX(CASE WHEN RefTypeId = 82 THEN Value END) AS [INTL PO#]
FROM dbo.ShipmentRefs
GROUP BY ShipmentId
What's the total row count on ShipmentRefs table?
Is there an index on ShipmentID, RefTypeID? Are the columns in a different order in the index? Does that index INCLUDE the value column?
Is that a pass-through query in Access?
What does the query plan look like (table scan, clustered/nonclustered scan)?
Do you really need 486K rows of output every time you run this?
From within access is the view being called directly? Or is there a filter?
That's actually pretty good for Access across a linked server. I'd say this is a non-issue. What are you expecting?
It's also not just a "view" You have a bunch of aggregates in the code, which probably means that it's looking at a whole lot more rows than 486K rows.
@robert_volk This is the Actual Execution plan:
The total row count for ShipmentRefs = 519k rows
This view returns 493k rows
The Clustered Index scan is on the PK (ShipmentID) which is an ID column.
Thanks.
here's the code:
SELECT ShipmentId, MAX(CASE WHEN RefTypeId = 1 THEN Value END) AS [Client BOL Number], MAX(CASE WHEN RefTypeId = 2 THEN Value END) AS [PO #], MAX(CASE WHEN RefTypeId = 3 THEN Value END) AS [Airway Bill #],
MAX(CASE WHEN RefTypeId = 5 THEN Value END) AS [Client ERP Sold To], MAX(CASE WHEN RefTypeId = 6 THEN Value END) AS [Client ERP Consignee Acct], MAX(CASE WHEN RefTypeId = 7 THEN Value END) AS [Trailer #],
MAX(CASE WHEN RefTypeId = 8 THEN Value END) AS [Container #], MAX(CASE WHEN RefTypeId = 9 THEN Value END) AS [Custom BOL#], MAX(CASE WHEN RefTypeId = 10 THEN Value END) AS [Sales Order Number],
MAX(CASE WHEN RefTypeId = 11 THEN Value END) AS [Sales Name], MAX(CASE WHEN RefTypeId = 12 THEN Value END) AS [Seal Number], MAX(CASE WHEN RefTypeId = 14 THEN Value END) AS [Order Type],
MAX(CASE WHEN RefTypeId = 15 THEN Value END) AS [BOL Stop Sequence], MAX(CASE WHEN RefTypeId = 16 THEN Value END) AS [Direction Code], MAX(CASE WHEN RefTypeId = 17 THEN Value END) AS [BOL Status Code],
MAX(CASE WHEN RefTypeId = 38 THEN Value END) AS [Niteo Shipper Ref 1], MAX(CASE WHEN RefTypeId = 39 THEN Value END) AS [Client Spot Quote Number], MAX(CASE WHEN RefTypeId = 40 THEN Value END)
AS [Client Spot Quote Amount], MAX(CASE WHEN RefTypeId = 41 THEN Value END) AS [Delivery #], MAX(CASE WHEN RefTypeId = 42 THEN Value END) AS [Shipment #], MAX(CASE WHEN RefTypeId = 43 THEN Value END)
AS [Flair Shipper #], MAX(CASE WHEN RefTypeId = 44 THEN Value END) AS [HOUSE BILL OF LADING #], MAX(CASE WHEN RefTypeId = 45 THEN Value END) AS [MASTER BILL OF LADING #],
MAX(CASE WHEN RefTypeId = 46 THEN Value END) AS [ORGILL PO #], MAX(CASE WHEN RefTypeId = 47 THEN Value END) AS [ORGILL GL CODE], MAX(CASE WHEN RefTypeId = 48 THEN Value END)
AS [Carrier Spot Quote Bid #], MAX(CASE WHEN RefTypeId = 49 THEN Value END) AS [EDI BOL #], MAX(CASE WHEN RefTypeId = 50 THEN Value END) AS [OB BOL #], MAX(CASE WHEN RefTypeId = 51 THEN Value END)
AS [Pick Slip #], MAX(CASE WHEN RefTypeId = 52 THEN Value END) AS [Customer Sales Order #], MAX(CASE WHEN RefTypeId = 53 THEN Value END) AS [Customer PO], MAX(CASE WHEN RefTypeId = 54 THEN Value END)
AS [INTL BOL #], MAX(CASE WHEN RefTypeId = 55 THEN Value END) AS [INTL Entry #], MAX(CASE WHEN RefTypeId = 56 THEN Value END) AS [INTL Entry Date], MAX(CASE WHEN RefTypeId = 57 THEN Value END)
AS [INTL Cube], MAX(CASE WHEN RefTypeId = 58 THEN Value END) AS [INTL # OF CONTAINERS], MAX(CASE WHEN RefTypeId = 59 THEN Value END) AS [Temp Control], MAX(CASE WHEN RefTypeId = 60 THEN Value END)
I've discovered that the PK Index is heavily fragmented (83%). Whereas on my Test server (which returns the result set in 15 seconds) it is 2 %. Is this likely the cause of slow performance on production? We will drop/recreate all indexes and the PK soon.
Rebuild not delete and recreate would be my recommendation
https://www.sqlshack.com/how-to-identify-and-resolve-sql-server-index-fragmentation/
Also do you use any stored procs from within your access app?
Definitely rebuild like @yosiasz suggested. Or, if you do a reorg, make sure to UPDATE STATISTICS ShipmentRefs WITH FULLSCAN. The rowcounts shown in the plan seem way off, and updating stats should fix that.
If your query is still slow, and you still see a big time difference over the Compute Scalar operation, you may want to do the PIVOT on the MS Access side, rather than all the MAX(CASE...) that you have. Basically:
SELECT ShipmentID, RefTypeID, MAX(Value) max_val
FROM ShipmentRefs
GROUP BY ShipmentID, RefTypeID
Make that a pass-through query in Access, then create a new Cross-Tab/Pivot query in Access with that as the source. Alternately you can create a cross-tab report to display it as well. This is assuming that you're only displaying it in Access.
The reason I suggest this is that your source of 519K rows is grouped down to 493K, which means 95% of your rows only have 1 RefTypeID, and you're computing a lot of cases that will end up as NULL (49 seconds to retrieve and almost 5 minutes to compute, although the operator times may be multiplied due to parallelism, therefore only valid for relative/ratio comparison). It may be that doing the pivoting in the presentation layer (Access) will be quicker, and it will certainly transmit less data over the network.
Lastly, if ShipmentRefs has many more columns that just the 3 in the query, then definitely create this index:
CREATE INDEX ShipmentID_RefTypeID
ON ShipmentRefs(ShipmentID, RefTypeID) INCLUDE(Value)
WITH (DATA_COMPRESSION=PAGE);
I know that most people don't know this, especially after following a "Best Practice" for more than 2 decades that's actually a WORST PRACTICE, don't REORGANIZE any index that fragments unless it is purely an "exploded broom tip" (Insert at end of ever increasing index and ONLY the new inserts are updated with expansive updates). It's just not worth it, especially log file wise and certainly not fragmentation wise.
I can also personally vouch against REORGANIZE in many cases even if the index is an "exploded broom tip" type of index. The logging will eat your face off. 
In a word, YES. The segment size of a heavily fragmented index can be quite low. Scans during a read typically stop as soon as a segment ends or before. A segment size of 1 ((the smallest segment size possible) is devastating to range and index scans.
I know you won't understand but that article and all others like it are totally incorrect because they recommend following supposed "Best Practices" that were never meant to be taken as Best Practices and are, in fact, WORST PRACTICEs. The article also never mentions Fill Factor, which makes it worse than the average bit of similar misinformation.
Please watch the following video and understand than it's NOT just about Random GUIDs. It's about several index myths we've all been suckered into following for more than two decades and what can actually be done about it. Keep in mind that this is ONLY and introduction to the much larger subject at hand and I'm working on it.
I have learnt a lot from you @JeffModen but was distracted by your condescending tone. You are a great talent but that is really useless if your people skills are fragmented
@yosiasz ,
My apologies. When I said I know you won't understand, I meant "until you watch the presentation.". Perhaps I should have said "You won't believe me until I show you" because my claim is so heterodoxic that no one ever does.
I, too, would have thought someone sat on the crackers if they had told me that Random GUIDs don't fragment or that the supposed "Best Practices" aren't. Can you tell me that you would have believed me if I just said "Random GUIDs" aren't a problem? I respectively submit that even someone as smart as you would have thought I lost my mind no matter how smart you may have previously thought me to be.
I'm sorry that you took it as an insult and I'll try to be more careful in the future. Again, it wasn't meant as an insult.
I appreciate everyone's ideas. I've learned from each of you and appreciate your patience as I scramble from one issue to another.
In my case, because this table is not used 24 hours and because it's not excessively large ..... I will use the drop and recreate option. In my limited
experience that has been the most effective way to drastically reduce fragmentation after other options have failed.
Thanks, guys!
ALTER INDEX indexnamehere ON schemanamehere.tablenamehere REBUILD
;
... will work much more quickly for Clustered Indexes and with much few resources than the DROP/RECREATE option.
The DROP/RECREATE option isn't the greatest for NON_CLUSTERED indexes, either. This will work much easier:
ALTER INDEX indexnamehere ON schemanamehere.tablenamehere DISABLE;
ALTER INDEX indexnamehere ON schemanamehere.tablenamehere REBUILD;
You must also be incredibly careful to NOT drop or disable any index that is UNIQUE being used as a target for FK's because, if you do, the DROP will cause an error that prevents the drop and DISABLE will produce a warning that any FK pointing to the column the index was based on will need to be dropped and rebuilt, as well. That could be a real problem if you have a whole lot of FK's pointing at the table.
@JeffModen THis db has FKs all over the flipping place so that's a useful warning.
After I restored the prod db to TEST I found that the Test table has near zero fragmentation on that PK. Does simply restoring clean up the fragmentation on Primary Keys?