Insert / Export Query to SQL Server

[lameid]

Let me begin by saying that no matter what I do that the biggest performance dog I face is the network in my environment, so things may just be slow but I was hoping someone may have some insight on best performance. Seriously, I see throughput around 20 Mbps. I sincerely wish it was 1999 with an NT 4 server and 100 Mbps Lan compared to this environment.

Currently I am running an Insert Into Query via code that appends to a linked SQL Server table. I linked it with the built in Microsoft driver "ODBC;DRIVER=SQL Server;" because ideally I want the process to work even if the native client driver is not installed. If it is significant issue, I am willing to switch to the native client.

Are there any thoughts on a faster way to load data short of putting the data local on the SQL box and have it process it directly? - This is not an option in my environment.

I was thinking that there may be an ADO solution to upload a disconnected recordset?

 

[dhookom]

Can you provide some specifics? We don't know how many records are inserted or your code or the number of users or the number of indexes.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

The SQL table has an Autoincrement BigInt Clustered index. There is also a composite key, with three fields that is unique. One Int field and two varchar.

The Composite key (Project, ProjectUID, RecipientType) logs data from multiple Projects (Access DB's) which have a Long integer "UID" on a denormalized table (source data we process) that may be used to generate messages to multiple recipient types. It is POSSIBLE that the same UID may be used for multiple recipient types. This key exists so access has a unique index to work with.

 

[lameid]

The record count may vary. I think the most at once has so far been around 80,000. Theoretically it could be as few as 1 but most often 15 is towards the lower limit. This is a different 'Project' than the other with 80,000.

 

[dhookom]

Lameid, you answered one of four questions.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

Writes are likely to be performed by three out of 5 users infrequently with minimal concurrency. Reads are to retrieve a specific record. There are 5 users here too and reads are more common.

An example from one of the project databases with some field name changes...

Sub LogMessageRegionalOwner(dtExtractDate as Date)

  sSql = "INSERT INTO dbo_tblMessage ( REGIONAL_ID, "
  sSql = sSql & " Project, "
  sSql = sSql & " ProjectUID, "
  sSql = sSql & " ExtractDate, "
  sSql = sSql & " MessageSentDate, "
  sSql = sSql & " MessageType, "
  sSql = sSql & " PDFPath, "
  sSql = sSql & " PDFFileName, "
  sSql = sSql & " RecipientType, "
  sSql = sSql & " Recipient_FN, "
  sSql = sSql & " recipient_LN, "
  sSql = sSql & " Recipient_ADDR_1, "
  sSql = sSql & " Recipient_ADDR_2, "
  sSql = sSql & " Recipient_CITY, "
  sSql = sSql & " Recipient_ST, "
  sSql = sSql & " Recipient_ZIP, "
  sSql = sSql & " Recipient_ZIP_4, "
  sSql = sSql & " Recipient_Fax )"
  sSql = sSql & " SELECT TD.REGIONAL_ID, "
  sSql = sSql & " """ & gConProj & """ As Project, "
  sSql = sSql & " MIN(TD.UID), "
  sSql = sSql & " TD.ExtractDate, "
  sSql = sSql & " TD.FaxRegionSent, "
  sSql = sSql & " ""FAX"" AS MessageType, "
  sSql = sSql & " """ & gConPDFPATH & """ & Format([ExtractDate],""yyyymmdd"") & ""\RegionalOwner\"" AS PDFPath, "
  sSql = sSql & " StandardFile(TD.ExtractDate, TD.[TemplateID], ""PR"", TD.REGIONAL_ID, TD.REGIONALID, TD.[REGIONAL_LN], Left(TD.[REGIONAL_FN], 1), TD.[REGIONAL_FAX], TD.DIVISION, TD.ProductListID, TDL.ProductlistName) AS PDFFileName, "
  sSql = sSql & " ""RegionalOwner"" AS RecipientType, "
  sSql = sSql & " TD.REGIONAL_FN, "
  sSql = sSql & " TD.REGIONAL_LN, "
  sSql = sSql & " TD.REGIONAL_ADDR_1, "
  sSql = sSql & " TD.REGIONAL_ADDR_2, "
  sSql = sSql & " TD.REGIONAL_CITY, "
  sSql = sSql & " TD.REGIONAL_ST, "
  sSql = sSql & " TD.REGIONAL_ZIP, "
  sSql = sSql & " TD.REGIONAL_ZIP_4, "
  sSql = sSql & " TD.REGIONAL_FAX"
  sSql = sSql & " FROM tblData TD"
  sSql = sSql & "   LEFT JOIN tblProductList as TDL ON TD.ProductListID = TDL.ProductListID "
  sSql = sSql & " WHERE (((TD.FaxRegionSent) Is Not Null) AND ((TD.PdfCreated_Region)=Yes))"
  sSql = sSql & "   AND TD.ExtractDate = #" & dtExtractDate & "#"
  sSql = sSql & " GROUP BY TD.REGIONAL_ID, "
  sSql = sSql & " TD.ExtractDate, "
  sSql = sSql & " TD.FaxRegionSent, "
  sSql = sSql & " TD.[TemplateID],"
  sSql = sSql & " TD.REGIONALID,"
  sSql = sSql & " TD.DIVISION,"
  sSql = sSql & " TD.ProductListID,"
  sSql = sSql & " TDL.ProductlistName,"
  sSql = sSql & " TD.REGIONAL_FN, "
  sSql = sSql & " TD.REGIONAL_LN, "
  sSql = sSql & " TD.REGIONAL_ADDR_1, "
  sSql = sSql & " TD.REGIONAL_ADDR_2, "
  sSql = sSql & " TD.REGIONAL_CITY, "
  sSql = sSql & " TD.REGIONAL_ST, "
  sSql = sSql & " TD.REGIONAL_ZIP, "
  sSql = sSql & " TD.REGIONAL_ZIP_4, "
  sSql = sSql & " TD.REGIONAL_FAX"
  sSql = sSql & ";"
  
  docmd.setwarnings False 'In reality I use a procedure that uses a static to count on and off and sets warnings appropriately
  DoCmd.RunSQL sSql 'StandardFile is user defined funtion must use application object model
  docmd.setwarnings True

End Sub

 

[dhookom]

Are both tblData and tblProductList Access tables?

We still don't know how many records are appended.

I would consider making a local table and then appending from the Access table to the SQL table. I will also use a temporary table on SQL server to upload into. Then run some append or update queries directly on the server.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

Yes Access source tables and the most recent similar case was 12,264, the max so far is 64,319. Some projects may only use tbldata (no product list) but this is the one I am having to wait on the most.

 

[dhookom]

Did you try any of my suggestions?

In the past, I have created a recordset in Access and looped through creating an INSERT statement that consists of a batch of 50 records at a time. The SQL ends up being something like:

INSERT INTO tblOrderDetails(OrderID, ProdID, Qty)
SELECT 234,'Chair',4
UNION SELECT 234, 'Table',2
UNION SELECT 234, 'Lamp',6
UNION SELECT 234, 'Rug', 3
-- etc --

Then execute the statement after a batch of 50 records have been unioned.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

Did you try any of my suggestions?

You made none prior to that post.

Chunking the data in makes a difference? I would think similar could be accomplished with ADO but that's where I am lost. My gut is saying there should be away to append records from one ADO connection to another. A disconnected ACE recordset feels like the place to start and then connect to SQL. But I had not gotten that deep into the belly of the beast before with ADO. Also there seems there would be a nuance to making the Recordset append only rather than replace the data. Or maybe some other way to stream the data than using a recordset.

 

[dhookom]

I posted a suggestion on 10/2

I would consider making a local table and then appending from the Access table to the SQL table. I will also use a temporary table on SQL server to upload into. Then run some append or update queries directly on the server.

I don't believe you have ever stated how long this takes. Is it minutes or hours?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

It is part of a doggedly slow series of procedures (in large part because those record counts are representative of documents being created, moved or sent)... Last I ran it was overnight. The Select statement runs in seconds on local Access Tables. I know ACE / Jet is stupid but how dumb can it really be at putting data in a table? I expect all kinds of horrid things if I join ODBC tables but to Insert into a table? Then again a simple temp table might help it along as it likes to run queries multiple times for the dumbest of reasons. I was starting to have my eye on TSQL's Bulk Insert. If I am creating a dataset anyway, why not a file and just import the file native in SQL Server? Oh right - I probably don't have the Bulk Insert Admin permission (read about it several hours ago, probably close but not right named permission).

It may be a while before I start toying with the kitchen sink methodology of try this or that to fix it without a definitive "this should help scenario".
I think Bulk Insert is my best option but also tricky to navigate both technically and bureaucratically (permissions). Life was so much easier when I was the Domain admin at a small company instead of one of the cogs in the machine.

 

[dhookom]

I think you need to split the records into smaller batches. When you attempt to insert the entire recordset, I believe it does this as a single transaction and your system is choking. Don't try to get the entire apple into your mouth at once. Take bites at a time and I expect it will take much less time.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016