Long delay with custom reports

Version 4
1

On my setup, I have a management screen that amongst other things runs two custom reports. This has been getting progressively slower and now produces a timeout error. Investigation of this led me to think it may be an MS SQL issue so first of all I upgraded to MSSQL 2014 and this made no difference. So then I ran some tests directly from the SQL management console as follows (SambaPOS program not running)

DBCC CHECKDB gives 0 allocation errors and 0 consistency errors (full text can be provided if it helps) and running the repair commands had no effect.

Running a simple script via the “new query” tab, to calculate the total amount billed since day 1

SELECT FORMAT(SUM([TotalAmount]),’#,###’) FROM [Tickets]
WHERE ([Tickets].[IsClosed] = 1)

gave timings as follows with the “new query” tab closed and reopened between each test
10.669 secs
10.813 s
10.938 s
If we now run the test WITHOUT closing the “new query” tab and instead just pressing “execute” we get timings as follows
0.046 secs
0.062 s
0.046 s
0.062 s
0.046 s
now returning to closing the “new query” window between test we go back to timings
10.771 s
10.712 s
10.793 s

The database has approximately 100,000 tickets and 380,000 transactions so is not excessively large.

If we now go back to SambaPOS and run the same script via custom reports, it takes around 11 secs the first time it runs and then too fast to measure if I simply press refresh. If I run another report and then go back to this one it is again very fast. If however, I close SambaPOS and then re-open it we are back to 11 secs again.

It seems therefore this very slow the first time it is run, but is OK after it has been run for the first time which seems odd as my understanding was each time a query is run it recalculates the data.

This bears out the operational problem we are having; when the management screen is loaded, it seems to hang until the reports are loaded (or sometimes gives a timeout error) but is OK after that even if you go out of the screen and back in again. The screenshot shows the timeout message screen layout in question.

Pasted image1366Ă—736 62 KB

I am trying to understand why I get these apparently odd results and what can be done about it to get the SQL queries running faster so would welcome any suggestions.

2

Upgrading to a SSD hard drive would probably help a lot. It seems pretty straight forward that it’s taking some time to query data on that drive and will get longer as you add more tickets.

You could store it as a value and then only read new tickets each WP adding those to that stored value eliminating the need to query every ticket each time.

3

Thanks for the suggestion, however, I doubt the drive has much to do with it as it is the same problem on both my development system which is a reasonably high spec and the live system, which is not so high spec. Additionally, it runs as expected when I don’t close the “new query” tab, so what is the difference between closing, or not closing, this tab. As I understand it, MSSQL reruns the query each time I click execute.

With regards getting worse as I add more tickets, whilst reading relevant topics on the forum, I read that Emre says he has had a system running with 3 million entries without any issues, so I find it hard to believe my 100k tickets is burdening an MSSQL database very much.

4

A SSD makes a huge difference with it. However can you show your template for the report? Show the script if your using SQL.

5

On management studio Can you enable execution plan and post the screen shot after executing query once?

6

I think because the execution plan is cached when you rerun query the second time. Closing the tab clears the cache. Or anyway, definitely something is being cached.

7

[Solved] On further investigation it turned out that many of the table indexes were significantly fragmented - up to 92% in some cases. I have rebuild all the offending indexes (although some still say they are fragmented) but now the query that previously took 10.7s, is running in less that 0.1s.

It is still marginally faster when run without closing the new query tab which is interesting but not really a concern as my operational difficulties are now resolved and SambaPOS is running nicely.

The execution plan (with the rebuilt indexes is shown below and seems to be saying the query would benefit from an index on dbo.[Tickets].([IsClosed])

Pasted image824Ă—300 23.6 KB

Any advice on whether that is worth doing would be appreciated.

8

Interesting stuff as there have been some other but rare similar complaints about “progressive slowness”. Might not be related but can I ask how did you rebuild the indexes and how to you select which ones to build?

9

In management studio, expand SambaPOS, expand Tables then expand the table of interest. Right click Indexes and choose Rebuild All. This will give you a display of whether the index is fragmented or not and you can either click OK or cancel.

This is a bit cumbersome and manual but OK for fault finding and investigation. A simple script could be written with ALTER INDEX and REBUILD commands, or there is a management plan script here https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html, but I am struggling to get my head round how to implement it with SQL Server Express which is mentioned in the FAQ.

10

Try this instead: Scheduling backup and maintenance for SQL Server Express

I use this on my web server which I am running a SQL Express 2014 database (not SambaPOS related, however this will work same for SambaPOS).

Even it is only for up to 2008, I confirm it works fine on 2014. The path to the SQLCMD.EXE will be different for SQL 2014, if you are using a 64bit system and have SQL 2014 64bit edition installed, the correct path should be:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE

Should also be same for 32bit system, but will be different if you have 32bit SQL installed on a 64bit system, likely only Program Files changed to Program Files (x86) though.

11

Thanks stevew, appreciate the reply.

I think @emre this would be a good future “database tool” included in SambaPOS to rebuild indexes. Most Restaurateurs are not as savy as Steve who can rebuild indexes through SQL Management Studio.

My thoughts only.