pcalabria
asked on
Need help writing a Pass Through Query in A2K
Hello,
I'm running an application written in Access 2K.
It has a split front/back end.
My InternetData table is on a MS SQL server
My Parts4Sales and ComponentMaster tables are Access mdb files.
The following query appends about 250,000 records and takes about 20 minutes to run. I need to speed it up.
I've been told I need to create a stored procedure or a pass through query.
First question, is this possible in as only one of the tables is on the MS SQL server
Second, if yes, will I get better performance out of the stored procedure or pass through?
Third, how can I find out how the SQL table is connected to the database. I set it up myself, but it was years ago.
I'm running an application written in Access 2K.
It has a split front/back end.
My InternetData table is on a MS SQL server
My Parts4Sales and ComponentMaster tables are Access mdb files.
The following query appends about 250,000 records and takes about 20 minutes to run. I need to speed it up.
I've been told I need to create a stored procedure or a pass through query.
First question, is this possible in as only one of the tables is on the MS SQL server
Second, if yes, will I get better performance out of the stored procedure or pass through?
Third, how can I find out how the SQL table is connected to the database. I set it up myself, but it was years ago.
ASKER
opps... here we go:
INSERT INTO InternetData ( OriginCode, LotCode, DateCodeAll, PartID, HouseNumber, ProjectHouseNumber, TrackingNumber, Description, PartClass, PartFamily, PartMarking, GenericNumber, Package, RecPrice, FullPacks, TrayQty, DocName, code, PackageQty, PackageType, Condition, ConditionCategory, PbookPrice, quantity, Manufacturer, SalesNote, Notes, LineID, [Value], Tolerance, FullReelQty, ReelSize, TapePitch, TapeWidth, CaseSize, TCR, Power, Voltage, OperatingRange, MSL, EURoHS, Pb, SearchNumber, OrderNumber ) SELECT Parts4Sales.OriginCode, Parts4Sales.Lotcode, Parts4Sales.DateCodeAll, Parts4Sales.PartID, Parts4Sales.HouseNumber, Parts4Sales.ProjectHouseNu mber, Parts4Sales.TrackingNumber , ComponentMaster.Descriptio n, ComponentMaster.PartClass, ComponentMaster.PartFamily , ComponentMaster.PartMarkin g, ComponentMaster.GenericNum ber, ComponentMaster.Package, Parts4Sales.RecPrice, Parts4Sales.FullPacks, Parts4Sales.TrayQty, ComponentMaster.DocName, ComponentMaster.code, Parts4Sales.PackageQTY, Parts4Sales.PackageType,
Parts4Sales.Condition, Parts4Sales.ConditionCateg ory, ComponentMaster.PBookPrice , Parts4Sales.Quantity, ManufacturerMaster.ShortNa me AS Manufacturer, Parts4Sales.SalesNote, ComponentMaster.Notes, Parts4Sales.LineID, ComponentMaster.Value, ComponentMaster.Tolerance, ComponentMaster.FullReelQt y, ComponentMaster.ReelSize, ComponentMaster.TapePitch, ComponentMaster.TapeWidth, ComponentMaster.CaseSize, ComponentMaster.TCR, ComponentMaster.Power, ComponentMaster.Voltage, ComponentMaster.OperatingR ange, ComponentMaster.MSL , ComponentMaster.EURoHs, ComponentMaster.Pb, Parts4Sales.searchNumber, ComponentMaster.OrderNumbe r FROM (Parts4Sales LEFT JOIN ComponentMaster ON (Parts4Sales.LineID = ComponentMaster.LineID) AND (Parts4Sales.SearchNumber = ComponentMaster.SearchNumb er)) INNER JOIN ManufacturerMaster ON Parts4Sales.LineID = ManufacturerMaster.LineID WHERE (((Parts4Sales.SearchNumbe r) Is Not Null And (Parts4Sales.SearchNumber) <>''));
INSERT INTO InternetData ( OriginCode, LotCode, DateCodeAll, PartID, HouseNumber, ProjectHouseNumber, TrackingNumber, Description, PartClass, PartFamily, PartMarking, GenericNumber, Package, RecPrice, FullPacks, TrayQty, DocName, code, PackageQty, PackageType, Condition, ConditionCategory, PbookPrice, quantity, Manufacturer, SalesNote, Notes, LineID, [Value], Tolerance, FullReelQty, ReelSize, TapePitch, TapeWidth, CaseSize, TCR, Power, Voltage, OperatingRange, MSL, EURoHS, Pb, SearchNumber, OrderNumber ) SELECT Parts4Sales.OriginCode, Parts4Sales.Lotcode, Parts4Sales.DateCodeAll, Parts4Sales.PartID, Parts4Sales.HouseNumber, Parts4Sales.ProjectHouseNu
Parts4Sales.Condition, Parts4Sales.ConditionCateg
ASKER
So the question is, is there a way I can speed up the above query. When running as an Access query, it takes about 20 minutes to run with 250,000 records.
All tables are in SQL Server? If not, which ones are and which ones not?
There's an index on Parts4Sales.SearchNumber? If not, you should create one for this query.
There's an index on ComponentMaster.SearchNumb er? If not, you should create one for this query.
There's an index on Parts4Sales.SearchNumber? If not, you should create one for this query.
There's an index on ComponentMaster.SearchNumb
ASKER
<<InternetData table is on a MS SQL server>>
<< Parts4Sales and ComponentMaster tables are Access mdb files.>>
The query is run from the an Access 2K front end, off an XP Pro workstation.
Both mdb files are on a Windows 2000 Server
The InternetData table is on a different workstations which is running MS Server 2008 ( I think its R2, its a paid version)
LineID in all tables is numeric, and it is indexed
Searchnumber in all tables is string, and it is also indexed
There are about 250,000 records in the query
When I convert to a select query and run it in the query builder I see results in about 3 seconds. When I then hit end to see the last record it takes about 30 seconds.
One of the other experts suggested I use a stored procedure or pass through, but I don't know how to do that so have not tried. It would think that since all the data is not on the SQL server, it may be complicated.
If anyone can help it would be greatly appreciated. I'm trying to get this working before the staff arrives at work in another hour! :-(
<< Parts4Sales and ComponentMaster tables are Access mdb files.>>
The query is run from the an Access 2K front end, off an XP Pro workstation.
Both mdb files are on a Windows 2000 Server
The InternetData table is on a different workstations which is running MS Server 2008 ( I think its R2, its a paid version)
LineID in all tables is numeric, and it is indexed
Searchnumber in all tables is string, and it is also indexed
There are about 250,000 records in the query
When I convert to a select query and run it in the query builder I see results in about 3 seconds. When I then hit end to see the last record it takes about 30 seconds.
One of the other experts suggested I use a stored procedure or pass through, but I don't know how to do that so have not tried. It would think that since all the data is not on the SQL server, it may be complicated.
If anyone can help it would be greatly appreciated. I'm trying to get this working before the staff arrives at work in another hour! :-(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the comments.
I mention that the select query can display all of the records in 30 seconds because it seems odd that I can pull 250,000 records in 30 seconds from Access MDB tables, but when I change the query to an APPEND query and append the same data to an MS-SQL server, it takes 20 minutes.
Doesn't that seem strange to you?
Anyone that can help, please chime in! Thanks Victor and Chaau.
I mention that the select query can display all of the records in 30 seconds because it seems odd that I can pull 250,000 records in 30 seconds from Access MDB tables, but when I change the query to an APPEND query and append the same data to an MS-SQL server, it takes 20 minutes.
Doesn't that seem strange to you?
Anyone that can help, please chime in! Thanks Victor and Chaau.
I don't work with Access for long. What do you mean with APPEND? It's a SELECT INTO?
ASKER
Thanks
1. It depends what you need to pass to the stored procedure as parameters. If you need to pass just values then it is possible to use The stored procedures. However, if your append query appends data directly from a SELECT statement from an Access table, I do not think you will get much performance improvements. As I said before, please show us your existing query, which indirectly answers your second question
3. You can find this information simply by looking at the "Linked table Manager". In Access 2000 this option is under Tools->Database Utilities->Linked Table Manager