VBA code / Query timeout (1 Viewer)

isladogs · May 22, 2017

Hi

Thought I'd ask a question for a change.
I've been looking at this for a couple of hours and a new set of eyes may help! I expect I'm missing something obvious

I have a large table of about 16000 records which contains details of all database objects including table fields, form row sources, module code etc etc

The table is used in routines to search for objects containing selected text strings and where necessary replace them (similar to VTools deep search)

The table is part of a linked SQL server BE

All works fine with one exception.

The table includes a boolean field 'Exclude' which can be set to true to omit selected objects from the search. After the search, it is reset to false
The field is indexed in the SQL table

Setting the field true for a large number of objects works quickly
However resetting false causes timeouts with anything more than about 200 records. I can't see any reason for this

I have tried increasingly complex code to:
a) turn off timeouts (set to 0)
b) loop through each time a batch of records is updated but it still fails after 2 or 3 loops

The code below is intended to reset all records to false
It includes both a) & b)

Code:

Private Sub cmdClearAll_Click()

On Error GoTo Err_Handler

    Me.lblInfo.visible = True
    Me.lblInfo.Caption = "Updating list of excluded items . . ."
    DoCmd.Hourglass True
    DoEvents

    
UpdateExclude:
    N = DCount("*", "tblDatabaseObjects", "Exclude = True")
   ' Debug.Print N
    'update display
    Me.LstExclude.Requery
    Me.lblExcludeTotal.Caption = "Total objects excluded = " & N
    Me.lblExcludedItemsTotal.Caption = Me.lblExcludeTotal.Caption
    DoEvents

    strSQL = "UPDATE DISTINCTROW tblDatabaseObjects SET tblDatabaseObjects.Exclude = False" & _
        " WHERE tblDatabaseObjects.Exclude = True;"
    
    CurrentDb.QueryTimeout = 0 'set to 0 to prevent timeout
    DoEvents
    CurrentDb.Execute strSQL, dbSeeChanges
    DoEvents
    CurrentDb.QueryTimeout = 60 'restore default=60s
    
    'clear controls
    For I = 1 To 6
        Me("txtExclude" & I) = ""
        Me("cboExclude" & I) = ""
        Me("cmdClear" & I).enabled = False
    Next
    
    'clear table tblDatabaseSQLSearchExcludedItems
    strSQL = "UPDATE DISTINCTROW tblDatabaseSQLSearchExcludedItems" & _
        " SET tblDatabaseSQLSearchExcludedItems.ExcludedItem = ''," & _
        " tblDatabaseSQLSearchExcludedItems.ObjectType = '';"
        
    CurrentDb.Execute strSQL, dbSeeChanges
    
    N = DCount("*", "tblDatabaseObjects", "Exclude = True")
    
    'update display
    Me.LstExclude.Requery
    Me.lblExcludeTotal.Caption = "Total objects excluded = " & N
    Me.lblExcludedItemsTotal.Caption = Me.lblExcludeTotal.Caption
    
    If N > 0 Then GoTo UpdateExclude
    
    Me.lblInfo.visible = False
    Me.lblInfo.Caption = ""
    DoEvents
    DoCmd.Hourglass False
        
    'disable current control
    Me.cmdExclude.SetFocus
    Me.cmdClearAll.enabled = False
    Me.cmdUpdateList.enabled = False

Exit_Handler:
    Exit Sub
    
Err_Handler:
    'err 3157- ODBC timeout
    If Err = 3157 Then GoTo UpdateExclude: 'Resume Next
    
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog

    Resume Exit_Handler

End Sub

BTW: I've tested with a local copy of the table and it works - just as you would expect.

pbaldy · May 22, 2017

Curious, it seems like relatively simple SQL and a relatively small table. I've never used DISTINCTROW in that situation. Does dropping it help? How about using a pass-through query so the processing is forced to the server?

JHB · May 22, 2017

I would use 0 instead of False.

Code:

tblDatabaseObjects.Exclude = 0;

isladogs · May 22, 2017

Hi
Thanks for replies

@pbaldy
I had added DISTINCTROW to see if that would help.
It doesn't make a difference either way so it may as well be removed.

Its very odd - as you say not an enormous table & very straightforward code
I've also tried it just using a query - same outcome
Its true that the 'SQL' field in the table does contain a lot of data but I don't see why that would affect the outcome
As I said previously, using a local table, the update is fine in 'both directions'

I've done all the obvious things - compact / decompile etc
I've also 'shrunk' the SQL datafile in SSMS
None of that has helped at all.
It still updates around 200 records, loops around & repeats then exits with error 3157

Hadn't thought about a passthrough query as I don't often use them
Certainly worth a try ... I'll let you know if it works

@JHB
Not sure why using 0 instead of False would make any difference but tried it anyway.
Unfortunately it didn't help

It appeared to go faster as evidenced by the form flickering at high speed
So I added Application.Echo False to fix that and the result was much the same as before ...

=========================================
ALSO...
1. The other odd thing is that if I run the update SQL as a query from the navigation pane, the records are updated instantly.
So I tried replacing the SQL in the form event with DoCmd.OpenQuery ...
and once again it just hangs.

So it must be something about the form itself....

2. I've got another linked SQL table with approx 1.4 million records and a boolean field, 'Protect'
I can update that field to True or False successfully from another form
It takes over a minute as there are a lot of records but both 'directions' take about the same time

The_Doc_Man · May 22, 2017

DISTINCTROW may be your time-eater. It implies a behind-the-scenes sort, so you are touching all records anyway (for the sort), after which you have to do yet another operating involving an update on selected records out of the implied temporary output of the implied sort. If you are going to touch everything anyway, I would just drop the DISTINCTROW and set it all to FALSE in one pass and be done with it.

EDIT: We apparently posted on top of each other. If DISTINCTROW doesn't seem to make a difference, that ain't it. However, re-reading the post, I see the WHERE clause. If you think about it, that ALSO is a time-eater because you STILL have to touch every record, make a decision, and set things FALSE. But heck, why bother. You want ALL of the records to be marked FALSE, you just weren't forcing FALSE where it already existed. I would drop the WHERE clause, too. Further, if there is a way to mark this as PASSTHRU then you would be able to start this query running and just not worry about it.

To my way of thinking, if this isn't a pass-through then you are still pulling all the records back to the FE host to do something so simple that you could easily trust the dumbest back-end in the world to do it. If THAT is the case, then the problem isn't your DISTINCTROW... it is the fact of having to process something in the FE because that query isn't being entrusted to SQL Server.

isladogs · May 22, 2017

Hi Doc

Thanks also

My original code was:

Code:

strSQL = "UPDATE tblDatabaseObjects SET tblDatabaseObjects.Exclude = False;"

It was like treacle ...

I only added the WHERE clause & the DISTINCTROW in increasingly frustrated attempts to speed things up. They didn't!

Using the code above as a query outside the report works instantly
(BUT then its very fast making the records True from inside the form ....!?!)

As I want to run it from the form, I'm going to try the passthrough query approach next

Minty · May 23, 2017

Do you have a lot of indexes on the underlying table?

I have a similar issue with one particular SQL table, and nothing seems to fix the issue. Inserting a new record (singular) takes an absolute age. Temporarily I removed most of the indexes and it certainly improved, but other queries / actions on that table then slowed to a crawl. Very Odd. Much bigger tables with similar properties simply don't have the issue.

isladogs · May 23, 2017

Hi Minty

Apart from the joint PK, only 1 index which I added to the 'Exclude' field to try & speed things up. Like everything else, it hasn't helped

Adding / deleting / editing records (apart from this field via the form) is acceptably fast.

I'm going to try removing the Exclude index later.

I also tried a passthrough query as suggested yesterday by pbaldy.
Even that hasn't helped.

It HAS to be something in the form itself as running a query to do the job works instantly.

So I'm going to re-create that cmdClearAll code step by step & see if I can fix the issue. Failing that I'll may have to recreate the form as a final resort ... but its got 50,000 + lines of code so not something I want to do

Here's a couple of screenshots to clarify the purpose of this form:

Exclude Mode:

Search Results:

static · May 23, 2017

I can't understand what your sql is doing or why you are using a loop.
Updating one field to a particular value should be one line of SQL.

isladogs · May 23, 2017

static said:
I can't understand what your sql is doing or why you are using a loop.
Updating one field to a particular value should be one line of SQL.

Hi

If you read post 1, it says that I resorted to increasingly desperate attempts to fix the issue. The timeout code, WHERE clause & loop were part of my attempts.

It started off as this:

Code:

strSQL = "UPDATE tblDatabaseObjects SET tblDatabaseObjects.Exclude = False;"
CurrentDb.Execute strSQL, dbSeeChanges

The loop was added as it updated approx 200 records then exited.
The idea of the loop was to do it repeatedly till I worked out the cause

I've since removed all extraneous code but haven't solved the issue.
As I said in the last response, even the passthrough query hasn't done the trick

static · May 23, 2017

Increasingly complex code doesn't solve simple issues.

Connect using ADO instead of DAO and see if that makes a difference.

CJ_London · May 23, 2017

the indexes of sql tables get increasingly slow if the table is subject to many changes to the indexed fields (and there is little benefit in indexing Boolean fields).

The solution is to rebuild the indexes, can't remember the code offhand but easy to google. Bit like compact and repair in access, but done of specified tables/indexes.

Here's one for doing it manually

https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx

isladogs · May 23, 2017

@Static

Yes I know .... & I would say that if I was replying to someone else.
But sometimes we all get caught up in a loop of our own making

I rarely use ADO & would therefore need to check how to modify the code

However I now have an alternative solution:

Before reading your post, I tried the following changes (one at a time) using the original one line update code:
a) added a timestamp field to the SQL table
b) removed the index from the Exclude field

I still got error 3157

I then tried emptying & repopulating the table & then the 'SQL' field used in the database search - all 16000 records. That didn't fix it either

Next I resorted to using a recordset - in direct contradiction of advice I gave to other forum users recently!
Of course it didn't help - error 3157 after about 150 records updated

Eventually, I redid the code completely using the fact that it all worked from an update query. As a workround, the code now:
a) closes the form
b) runs the update query
c) reopens the form
d) resets various controls

Code:

Private Sub cmdClearAll_Click()

On Error GoTo Err_Handler

    Me.lblInfo.visible = True
    Me.lblInfo.Caption = "Updating list of excluded items . . ."
    DoCmd.Hourglass True
    DoEvents
    
    'close the form
    Application.Echo False
    DoCmd.Close acForm, Me.Name
    DoEvents
    
    'run update query
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUpdateDatabaseObjectsSetExcludeFalse"
    DoCmd.SetWarnings True
    DoEvents
    
    'reopen the form
    DoCmd.OpenForm "frmDatabaseSQLSearch"
    DoEvents
    
    Set frm = Forms!frmDatabaseSQLSearch
    
    'reset form to previous state
    ShowControls False, "Q", "S" 'hide search & results controls

    N = DCount("*", "tblDatabaseObjects", "Exclude = True")
    frm.lblExcludeTotal.Caption = "Total objects excluded = " & N
    frm.lblExcludedItemsTotal.Caption = frm.lblExcludeTotal.Caption
    frm.Requery
    
    frm.LstExclude.Requery
    
    ShowControls True, "E"
        
    frm.cmdExclude.Caption = "Hide Exclude List"
    
    If N = 0 Then
        MsgBox "There are currently no excluded items", vbInformation, "All items are included in search results"
    Else
        frm.cmdClearAll.enabled = True
    End If
    
    'clear controls
    For I = 1 To 6
        frm("txtExclude" & I) = ""
        frm("cboExclude" & I) = ""
        frm("cmdClear" & I).enabled = False
    Next
    
    'clear table tblDatabaseSQLSearchExcludedItems
    strSql = "UPDATE tblDatabaseSQLSearchExcludedItems" & _
        " SET tblDatabaseSQLSearchExcludedItems.ExcludedItem = ''," & _
        " tblDatabaseSQLSearchExcludedItems.ObjectType = '';"
        
    CurrentDb.Execute strSql, dbSeeChanges
    
    Application.Echo True
    
EndRoutine:
    frm.lblInfo.visible = False
    frm.lblInfo.Caption = ""
    DoEvents
    DoCmd.Hourglass False
        
    'disable current control
    frm.cmdExclude.SetFocus
    frm.cmdClearAll.enabled = False
    frm.cmdUpdateList.enabled = False
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog

    Resume Exit_Handler

End Sub

I've just tested this on approx 1000 records & it took less than a second
As a further test, I set all 16000 records with Exclude=true
I then ran the new code again - all reset in less than 2 seconds.

I'm going to call that a success.
Of course its a botch but it works
I'm not sure I want to spend any more time finding a 'proper' solution for this.

Many thanks for everybody's help & ideas - much appreciated

isladogs · May 23, 2017

CJ_London said:
the indexes of sql tables get increasingly slow if the table is subject to many changes to the indexed fields (and there is little benefit in indexing Boolean fields).

The solution is to rebuild the indexes, can't remember the code offhand but easy to google. Bit like compact and repair in access, but done of specified tables/indexes.

Here's one for doing it manually

https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx

Hi CJ

Thanks
Didn't see this before posting my last response

Although I forgot to mention it, I'd already tried manually rebuilding the PK index (after removing the pointless boolean index).
Unfortunately it also didn't help - as before approx 150-200 records updated then the code exited

So I'm going to stick with my 'workround botch solution'.

Meanwhile in case its of any use to others, here's some code I already have for reindexing an entire SQL datafile.
Its specific to my own db but could easily be made general

Code:

Public Sub ReindexDatafile()

'On Error GoTo Err_ReindexDatafile

'================================
'Check link details for SDABE datafile
    strSQL1 = "SELECT tblTableLinks.TableName, tblTableLinks.TableAlias, tblTableLinks.LinkActive, tblTableLinkTypes.LinkType, tblTableLinkTypes.LinkServer, tblTableLinkTypes.LinkDatabase, tblTableLinkTypes.LinkUsernamePassword, tblTableLinkTypes.LinkUsername, tblTableLinkTypes.LinkPassword" & _
                " FROM tblTableLinkTypes INNER JOIN tblTableLinks ON tblTableLinkTypes.TableLinkType = tblTableLinks.LinkType" & _
                " WHERE tblTableLinks.TableAlias='PupilData';"
        
    Set db = CurrentDb
    Set MyRset = db.OpenRecordset(strSQL1, dbOpenSnapshot)
    
'Identify SQL link parameters for SDABE datafile
    strCurrentLink = "ODBC;DRIVER=SQL Server;SERVER=" & MyRset!LinkServer & ";Database=" & MyRset!LinkDatabase & ";UID=" & MyRset!LinkUsername & ";PWD=" & MyRset!LinkPassword
   ' Debug.Print strCurrentLink

'================================
'Check if qryTempPassthrough already exists
N = 0

For Each qdfTemp In db.QueryDefs
    If qdfTemp.Name = "qryTempPassthrough" Then N = 1
Next

'If query exists . . .delete it!"
If N = 1 Then
    db.QueryDefs.Delete "qryTempPassthrough"
End If
'================================

Set qdfPassThrough = db.CreateQueryDef("qryTempPassthrough")
   
qdfPassThrough.Connect = strCurrentLink

'Today's date in format 20090728 as used in naming backup file
Today = Right(Date, 4) & Mid(Date, 4, 2) & Left(Date, 2)   'CR
'Current time in format 1400 as used in naming backup file
BackupTime = Left(Time, 2) & Mid(Time, 4, 2)
strBackupDateTime = [Today] & [BackupTime]

'================================
'Setup backup conditions based on datafile connection & type

Select Case strCurrentLink
       
    Case "ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;Database=SDABE_StB;UID=SDAuser;PWD=SDApassword"
        'Local datafile e.g. CR laptop ; SDABE
        
        'Load backup file settings - OLD expressmaint code
            qdfPassThrough.SQL = "USE Master; EXEC expressmaint" & _
                                " @database      = 'SDABE_StB'," & _
                                " @optype        = 'REINDEX'," & _
                                " @reportfldr    = 'G:\SQLbackups\Reports'," & _
                                " @rptretainunit = 'days'," & _
                                " @rptretainval  = 1," & _
                                " @report        = 1;"
        
            'qdfPassThrough.SQL = "BACKUP DATABASE [SDABE_StB]" & _
                                " TO DISK = N'G:\SQLBackups\SDABE_StBerns\SDABE_FullDBBackup_" & _
                                    strBackupDateTime & ".BAK'" & _
                                " WITH NOFORMAT," & _
                                " NOINIT," & _
                                " NAME = N'SDABE-Full Database Backup'," & _
                                " SKIP," & _
                                " NOREWIND," & _
                                " NOUNLOAD," & _
                                " STATS = 1;"
                                
            strBackupFolder = "G:\SQLbackups"
            strDB = "SDABE"
                                
End Select
            
    'Run the backup..
   qdfPassThrough.ReturnsRecords = False
   qdfPassThrough.Execute
   
   MsgBox "The SQL datafile has been successfully reindexed.  " & vbNewLine & vbNewLine & _
        "The backup file is called " & strDB & "_FullDBBackup_" & strBackupDateTime & ".BAK   " & vbNewLine & _
        "It has been saved in the folder " & strBackupFolder & "\" & strDB, vbInformation, "SQL Backup completed"
   
'Delete the temp query & close the database
   db.QueryDefs.Delete "qryTempPassthrough"
   db.Close
   

End Sub

P.S. Are you still getting email notifications?

NauticalGent · May 23, 2017

ridders said:
...Of course its a botch but it works
I'm not sure I want to spend any more time finding a 'proper' solution for this...

I realize I am swimming in the deep end of the pool and I am not even going to pretend I understand the issue.

But I will say this: IMHO, Access behaves better with "batch" (queries) processing as compared to record (looping) through recordsets.

The monster I inherited was EXTREMLY VBA oriented and there were many issues with performance and sometimes even data corruption. I suspected the corruption was due to multiple open RS's which were updated while the users were online. Since my grip on VBA was (is!) tenuous at best, I converted as much as I could to action queries. That was back in Oct and since the application runs much faster and has not crashed since (knock on wood).

So...a botch it may be, but given my circumstance, I will take the "botch" to a "Proper" six days of the week and twice on Sunday!

isladogs · May 23, 2017

IMHO, Access behaves better with "batch" (queries) processing as compared to record (looping) through recordsets.

Agreed, I only tried a using a recordset after trying everything else.
In fact I'd only recently told 2 other forum users to scrap unnecessary recordsets and use update query code instead!

So...a botch it may be, but given my circumstance, I will take the "botch" to a "Proper" six days of the week and twice on Sunday!

After several hours on this, me too...

The_Doc_Man · May 23, 2017

Reading the activity here since my last post, I have a few more thoughts that might be randomly helpful.

You have a form open and were trying to reset the Exclude flag to FALSE through a query triggered in the form's class module, or at least that is what I think I saw. The mystery that you are finding is that it works better if you close the form.

That tells me that the locking parameters are too strict. You need the form locking option to be set to either NONE or OPTIMISTIC and you need to set the separate query's locking to NONE. That plus making it a pass-through should make this query blaze with its speed.

The business of working better in an SQL query from direct execution than from being triggered AS SQL from inside a form is a chimera. They both do exactly the same thing - they pass an SQL string from the workstation to the database engine. But the database engine does not do the same thing because the form is closed in one case and therefore cannot exert any locking influence. And since this putative locking would be network-related, there would be some network traffic associated with it. I.e. the "Windows Way" of locking involves checking for EACH RECORD whether it is open by someone else because of your dbSeeChanges option. Get rid of that. You don't want to see changes while the query is executing. You want it to execute in record-quick time and you KNOW what the changes will be. The only thing you might care about is if you run this query in the background of your VBA on the form, run the query and then do a Me.Refresh or even a Me.Requery (depending on what else the form has to do.)

Using the Windows Performance Monitor, you could probably prove this point by tracking the network activity level for the two cases. Of course, I could be thinking the wrong thing here, but I have seen really nasty effects when locking was an issue and the remote BE was on a less-than-blazing network.

As to the other issue, just a comment on indexing a Boolean field. There is this property called the cardinality which is, in simplest terms, an estimate of how many records would be returned for one of the possible values of an indexed field. The extremes for a table of n records are: For Booleans, you get back n/2 records; for something that is a prime key you get back 1 record. So for your 16,000 records, the expectation value is 8,000 records - not a very selective index. And worse, your problem is that (as you suggested) you have an uneven distribution. A badly skewed data set makes indexes perform even worse because the internal "tree" that they form is not very efficient. I'm not sure whether they use B-tree or something else behind the scenes, but a large, skewed, and therefore badly unbalanced tree will be a pure b|tch to maintain.

static · May 23, 2017

Botch or not it looks better than the first lot of code to me.

You are using too many doevents though and I wonder why you're (trying to) close the form that's running the code?

Minty · May 23, 2017

Here is an Stored Procedure index rebuild in SQL ; you can adjust the fragmentation levels and page sizes to suit you needs - it uses sp_send_dbmail to send you a list of what it's done.

Code:

USE [YourSQLDatabase]
GO
/****** Object:  StoredProcedure [dbo].[sp_Index_Rebuild]    Script Date: 23/05/2017 15:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_Index_Rebuild]  
AS  
BEGIN  
SET NOCOUNT ON
DECLARE 
    @objectid int,
    @indexid int,
    @objectname varchar(150),
    @indexname varchar(150),  
    @indextype varchar(150),
    @avgfragperc_a decimal,
    @avgfragperc_b decimal,
    @msg varchar(MAX),  
    -- Fill this parameters to be used in sp_send_dbmail
    @mail_profile_name varchar(50) = '',
    @mail_recipients varchar(50) = 'youremail@yourco.com',
    @mail_copy_recipients varchar(50) = ''
   
CREATE TABLE #fraglist (  
   ObjectId int,
   ObjectName varchar(150),  
   IndexId int,
   IndexName varchar(150),  
   IndexType varchar(150),  
   AvgFragPercent_before decimal,
   AvgFragPercent_after decimal
)  
INSERT INTO #fraglist ( ObjectName, IndexName, IndexType, AvgFragPercent_before, ObjectId, IndexId )
SELECT ( SELECT UPPER(name)
           FROM sys.objects
          WHERE object_id = dm.object_id ) ObjectName,
       ( SELECT UPPER(name)
           FROM sys.indexes
          WHERE object_id = dm.object_id
            AND index_id = dm.index_id ) IndexName,
       dm.index_type_desc, avg_fragmentation_in_percent, dm.object_id, dm.index_id
  FROM sys.dm_db_index_physical_stats(db_id(), 0, -1, null, null) dm
 WHERE avg_fragmentation_in_percent > 8      [COLOR="Green"]-- These values can be changed to suit[/COLOR]
   AND index_id > 0
   AND page_count >= 50
IF (@@ROWCOUNT = 0)
 RETURN
-- CURSOR FOR FRAGMENTED OBJECTS REASONABLE --
DECLARE cs CURSOR FORWARD_ONLY LOCAL FOR  
SELECT ObjectName, IndexName, IndexType, AvgFragPercent_before, ObjectId, IndexId
  FROM #fraglist
 ORDER BY ObjectName, IndexName
OPEN cs  
FETCH NEXT FROM cs INTO @objectname, @indexname, @indextype, @avgfragperc_b, @objectid, @indexid
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  EXEC ('ALTER INDEX [' + @indexname + '] on ' + @objectname + ' REBUILD WITH (FILLFACTOR = 85)')
    
  FETCH NEXT FROM cs INTO @objectname, @indexname, @indextype, @avgfragperc_b, @objectid, @indexid
END;  
  
CLOSE cs;  
DEALLOCATE cs;  
UPDATE #fraglist
   SET AvgFragPercent_after = avg_fragmentation_in_percent
  FROM #fraglist
       INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), 0, -1, null, null)
          ON object_id = ObjectId
         AND index_id = IndexId
    
-- CURSOR CS_REPORT  
DECLARE cs_report CURSOR FORWARD_ONLY LOCAL FOR
 SELECT ObjectName, IndexName, IndexType, AvgFragPercent_before, AvgFragPercent_after
   FROM #fraglist  
  ORDER BY ObjectName, IndexName
  
OPEN cs_report;  
FETCH NEXT FROM cs_report INTO @objectname, @indexname, @indextype, @avgfragperc_b, @avgfragperc_a
  
SELECT @msg = '<font size=2 align="right"><H4>Below rebuilded indexes:</H4><BR>' +   
              'Labels:<BR>'+  
              'FB: Percent of current logical fragmentation<BR>'+  
              'FA: Percent of fixed logical fragmentation<BR>'+  
              'The best values are between 0% and 5%<BR><BR>'+  
              '<table border="1">'+  
              '<tr>'+  
              '<th>Object</th>'+  
              '<th>Index</th>'+  
              '<th>Index Type</th>'+  
              '<th>FB %</th>'+  
              '<font color=blue>'+  
              '<th>FA %</th>'+  
              '</font>'+  
              '</tr>'  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SELECT @msg = @msg +   
                '<tr>'+  
                '<td>'+ @objectname +'</td>'+  
                '<td>'+ @indexname +'</td>'+  
                '<td>'+ @indextype +'</td>'+  
                '<td>'+ CAST(@avgfragperc_b AS VARCHAR(10)) +'</td>'+  
                '<font color=blue>'+  
                '<td>'+ CAST(@avgfragperc_a AS VARCHAR(10)) +'</td>'+  
                '</font>'+  
                '</tr>'  
  
  FETCH NEXT FROM cs_report INTO @objectname, @indexname, @indextype, @avgfragperc_b, @avgfragperc_a
END;  
  
SELECT @msg = @msg +   
        '</font>'+  
              '</table>'  
  
CLOSE cs_report;  
DEALLOCATE cs_report;  
  
EXEC msdb.dbo.sp_send_dbmail   
    --@profile_name    = @mail_profile_name,  
    @recipients      = @mail_recipients,  
    @copy_recipients = @mail_copy_recipients, -- Optional     
    @body            = @msg,  
    @body_format     = 'HTML',  
    @importance      = 'High',  
    @subject         = 'Rebuilt indexes report'  
  
DROP TABLE #fraglist;

isladogs · May 23, 2017

Hi

I can't keep up with all these very helpful replies...

@Doc_Man

The mystery that you are finding is that it works better if you close the form.
That tells me that the locking parameters are too strict. You need the form locking option to be set to either NONE or OPTIMISTIC and you need to set the separate query's locking to NONE. That plus making it a pass-through should make this query blaze with its speed.

The form locks are set to NONE. So are the separate query.
I agree that the update routine should be have been extremely fast within the form whether using a passthrough or not.
But the fact remains it was unacceptably slow with repeated timeout errors

I added dbSeeChanges when I got an error message which said it was necessary for a SQL table with indices
Since then, I've been adding it almost by default - I'll stop doing so unless the error message recurs

Thanks for the detailed explanation of indexing.
I understood that & could possibly answer test questions on it this time ...

I only added the index to the Exclude field as one of many attempts to get it to work
I later removed the index, the WHERE clause, DISTINCTROW & the code loop as doing nothing much except make it more complicated

@Static

Botch or not it looks better than the first lot of code to me.

I agree - mainly because it works this time!
Don't forget my first post wasn't the original code which 1 line of SQL code - no loop, no WHERE clause & no DISTINCTROW
BUT it dodn't work ....

You are using too many doevents though and I wonder why you're (trying to) close the form that's running the code?

You're probably right about multiple DoEvents - I just wanted it to finally work without hiccups!
It did so I left them in
I've explained above & in previous posts why it was necessary to close the form even though it makes no sense to have to do this.

@Minty
Thanks for the stored procedure - probably one to add to the Code Repository?
I'll try & get my head around what its doing & may come back to you if I'm unclear about what it all means
Can you (or anyone) recommend a good reference for SPs?

======================

I'm now treating this as solved

However for now, I'm reluctant to mark the thread as such due to the high quality & range of the input from so many of the site experts...

Many thanks to all of you who responded:
pbaldy; JHB; The_Doc_Man; Minty; static; CJ_London; Nautical Gent

VBA code / Query timeout (1 Viewer)

MVP / VIP

Wino Moderator

Have been here a while

MVP / VIP

Immoderate Moderator

MVP / VIP

AWF VIP

MVP / VIP

Attachments

Registered User.

MVP / VIP

Registered User.

Super Moderator

MVP / VIP

MVP / VIP

Ignore List Poster Boy

MVP / VIP

Immoderate Moderator

Registered User.

AWF VIP

MVP / VIP

Similar threads

Users who are viewing this thread