Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: slow running queries

    All; I really need help on this. Let me start from the beginning. Before I upsized to sql server 2008 this was and in fact still the scenirio. User import an excel file into a temporary table in access. The reason for the temp table is because the excel file they import isn't complete. They wanted fields to autofil based on data from other fields which are in another table in the database. So, when they import the excel spreadsheet, the field they want autopopulated is already a column in the spreadsheet. Some of the records already has data but the majority of them do not. This spreadsheet also contains address fields which have zipcodes and they wanted to have the city and state populate based on the zipcode field. The zipcode table was pulled from the internet and updated periodically by the admin. Here are some of the update queries:

    Code:
    UPDATE tblDTF_SHIPMENT_TEMP
       INNER JOIN dbo_tblZipcode
          ON tblDTF_SHIPMENT_TEMP.RECIPIENTZIP = dbo_tblZipcode.txtZIPCODE
       SET
          tblDTF_SHIPMENT_TEMP.RECIPIENTCITY = [dbo_tblZipcode].[txtCity]
    ,     tblDTF_SHIPMENT_TEMP.RECIPIENTSTATE = [dbo_tblZipcode].[txtState];
    
    
    
    UPDATE tblDTF_SHIPMENT_TEMP
       INNER JOIN dbo_tblCarrierID
          ON tblDTF_SHIPMENT_TEMP.CARRIERID = dbo_tblCarrierID.txtCARRIERID
       SET tblDTF_SHIPMENT_TEMP.CARRIERNAME = [dbo_tblCARRIERID].[txtCARRIERNAME];
    
    
    
    UPDATE tblDTF_SHIPMENT_TEMP
       INNER JOIN qryCOUNTRYREGION
          ON tblDTF_SHIPMENT_TEMP.RECIPIENTSTATE = qryCOUNTRYREGION.txtSTATE
       SET tblDTF_SHIPMENT_TEMP.COUNTRYREGRECIPIENT = [qryCOUNTRYREGION].[txtREGIONDESC];
    I know the rules of normalization but this spreadsheet is the way the users get it from different companies they deal with and they wanted it in the database as is. So; I accomodated them. I just created a function in Access using docmd transferspreadsheet and update queries to update all the records and called the it from a command button. It didnt take no time at all. The problem is now that the tables are in sql linked to access frontend; this function takes about 20mins to run! Its updated all records for each query. I don't have much experience in stored proc but will to learn. I just need advice on what to change so this can run faster.
    Thank you very much.

  2. #2
    Join Date
    Jul 2004
    Posts
    214
    Sorry; I just realized this is sort of a duplicate post with more explantion. But; I am despart for help!
    Thanks

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So far you haven't provided the information that I would need in order to give you much help. Let's see if we can get more of that information, thporen maybe we can be more help!

    First order of business, I need to understand you and your SQL Server related skills. You appear to be a new but promising MS-Access developer that has been thrown pretty much "head first" into the SQL Server swimming pool. You understand MS-Access and how to get it to do most of the things that you (and your users) want it to do, but you're relatively new to large databases in general and SQL Server databases in specific.

    I assume that you have little or nothing in the way of IT support. There isn't anyone handy to ask questions about server configurations, network infrastructure, etc.

    Please either confirm, clarify, or completely revise these assumptions.

    Now for the more specific questions, which are usually easier. What Version of SQL Server are you using? The easy way to find that is to capture the output from:
    Code:
    SELECT @@version
    Erk, I got distracted and sent this message a bit too soon! Picking up again here.

    Do you have a place to put a scratch copy of this database? Another server would be best, but if you have enough disk on your dev or production servers those would work too.

    Give us that much information, and we can probably start to help more.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    Hi Pat,
    Most of your assumptions are dead on; especially about being thown in the river without a paddle. I am using Sql Server 2008. I may have room on the
    sql instance they gave me access to put a test database. IT is being territorial. I am an Access Develper with a minor in SQL. It's been 5yrs since I have had to wear this hat with SQL 2005 mostly developing. Not very comfortable with DBA or take overs. I hope this helps.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, then let's start simply:
    1. Start a trace using SQL Profiler
    2. Start a backup from SQL Server Management Studio
    3. After the backup completes, start running your application
    4. Spend some time using your app, especially the "slow" parts
    5. Stop the trace you have running in SQL Profiler
    6. Restore the backup taken above to a test database
    7. Use SQL Profiler to extract the SQL from the trace file
    8. Copy the SQL statements from the completion of the backup through the end of your test
    9. Play the copied SQL statements into the test database using SSMS Analyze Query in Database Engine Tuning Advisor
    10. Copy the suggestions from the Database Engine Tuning Advisor, and paste them here for us to help analyze.
    I realize that this list seems complex, but it really isn't hard as long as you have enough disk space for the test databaase. Ask questions if you would like steps clarified or explained in more detail.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jul 2004
    Posts
    214
    Ok. I will do this as soon as we get back from the holiday.. Thank you

  7. #7
    Join Date
    Jul 2004
    Posts
    214
    These are the recommendations from Database Engine Tuning Advisor:

    Code:
    CREATE STATISTICS [_dta_stat_290100074_19_20] ON [dbo].[tblLTL_SHIPMENT_temp]([SENDERCITY], [SENDERSTATE])
    Code:
    CREATE STATISTICS [_dta_stat_290100074_1] ON [dbo].[tblLTL_SHIPMENT_temp]([INVOICENUM])
    Code:
    CREATE NONCLUSTERED INDEX [_dta_index_tblLTL_SHIPMENT_temp_12_290100074__K2] ON [dbo].[tblLTL_SHIPMENT_temp] 
    (
    	[CARRIERID] ASC
    )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    Code:
    CREATE NONCLUSTERED INDEX [_dta_index_tblZipcode_12_613577224__K3_K4] ON [dbo].[tblZipcode] 
    (
    	[txtCITY] ASC,
    	[txtSTATE] ASC
    )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    Thanks

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Those suggestions (at least some of them) aren't complete, you didn't get the whole text of what was provided. The parts that you've posted seem like good ideas to me, although I would have expected several more index suggestions.

    Try to use the suggestions in the test copy of your database. Apply the suggestions, then try to run your MS-Access application and see how it performs. Let us know how your application runs after you apply the suggestions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jul 2004
    Posts
    214
    Ok. I will do as suggested. Here's a few more that I didn't copy the first time. I thought I had it all.

    Code:
    CREATE STATISTICS [_dta_stat_290100074_1] ON [dbo].[tblLTL_SHIPMENT_temp]([INVOICENUM])
    CREATE STATISTICS [_dta_stat_290100074_19_20] ON [dbo].[tblLTL_SHIPMENT_temp]([SENDERCITY], [SENDERSTATE])
    I am stuck on creating the statistics. I tried to find a script template in SQL but no luck. Will browse the internet.
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •