Difference between revisions of "Size Comparison of SwSQL with MS SQL"

From support-works
Jump to navigation Jump to search
(Created page with "{{Template:Basic Cover |title=How to Repair a Corrupt Index File |type=FAQ |htl=Y }} {{Template:Basic Status |status=Published |version=1.0 |authors=Hornbill Support |applic...")
 
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
 
{{Template:Basic Cover
 
{{Template:Basic Cover
−
|title=How to Repair a Corrupt Index File
+
|title=Size Comparison of SwSQL with MS SQL
 
|type=FAQ
 
|type=FAQ
 
|htl=Y
 
|htl=Y
Line 7: Line 7:
 
{{Template:Basic Status
 
{{Template:Basic Status
 
|status=Published
 
|status=Published
−
|version=1.0
+
|version=1.1
−
|authors=Hornbill Support
+
|authors=HTL QA
 
|applicableto=Supportworks ESP
 
|applicableto=Supportworks ESP
 
}}
 
}}
 +
== Size Comparison of SwSQL with MS SQL ==
  
 +
The  chart below compares the amount of space various quantities of specific  types of database content stored by Supportworks will occupy in  SwSQL  and MS SQL, respectively. The two size columns show the cumulative  totals as you keep adding the content indicated. Note that the space  taken up by transaction logs is not included, so that a like-for-like comparison can be given.
  
−
== How to Repair a Corrupt Index File ==
+
{| width="90%" border="1" cellspacing="0"
−
This  document details how to resolve an issue with a corrupt index file  (extension .MYI) in the MySQL database. The error is reported as follows  in the log file:
+
|-
 +
| width="50%" scope="col" | '''Database Content'''
 +
| width="12%" scope="col" | '''SwSQL Actual Size (KB)'''
 +
| width="12%" scope="col" | '''MS SQL Actual Size (KB)'''
 +
|-
 +
| Blank database
 +
| 0
 +
| 3145
 +
|-
 +
| Clean installation
 +
| 232
 +
| 3145
 +
|-
 +
| 1 site record
 +
| 236
 +
| 3145
 +
|-
 +
| 1 charge centre record
 +
| 239
 +
| 3145
 +
|-
 +
| 1 customer record
 +
| 248
 +
| 3145
 +
|-
 +
| 10 site records
 +
| 249
 +
| 3145
 +
|-
 +
| 100 customer records
 +
| 263
 +
| 3145
 +
|-
 +
| 1 logged call (1 record in ''opencall'' + ''updatedb'' with 1000 words)
 +
| 303
 +
| 3145
 +
|-
 +
| 10 calls logged
 +
| 395
 +
| 3145
 +
|-
 +
| 50 calls logged
 +
| 754
 +
| 3145
 +
|-
 +
| 100 calls logged
 +
| 1177
 +
| 3145
 +
|-
 +
| 200 calls logged
 +
| 2155
 +
| 4194
 +
|-
 +
| 300 calls logged
 +
| 3114
 +
| 5242
 +
|-
 +
| 400 calls logged
 +
| 4044
 +
| 6291
 +
|-
 +
| 500 calls logged
 +
| 4981
 +
| 7340
 +
|-
 +
| 600 calls logged
 +
| 5912
 +
| 8388
 +
|-
 +
| 700 calls logged
 +
| 6857
 +
| 9437
 +
|-
 +
| 800 calls logged
 +
| 7777
 +
| 10485
 +
|-
 +
| 900 calls logged
 +
| 8710
 +
| 11534
 +
|-
 +
| 1000 calls logged
 +
| 9652
 +
| 12582
 +
|-
 +
| 1000 calls then updated with 1 update of 1000 words
 +
| 18246
 +
| 20971
 +
|-
 +
| 1000 calls then updated with 2nd update of 1000 words
 +
| 26756
 +
| 30408
 +
|-
 +
| 1000 calls then updated with 3rd update of 500 words
 +
| 31152
 +
| 38797
 +
|}
  
−
Can't open file: '''TABLE-NAME''.MYI'. (errno: 145)
+
=== General Observations ===
  
−
In order to resolve this, please follow the instructions below:
+
* For a service desk with a rapid turn-around  of calls (and therefore a low to medium volume at any given time), the   MS SQL database is substantially larger than the corresponding MySQL  database.
−
 
+
*  For a service desk with a high volume of calls, with one or two  updates  being required on average, the difference reduces to about 10%-15%.
−
#  Run the Supportworks Server Configuration utility. (Start > Programs  > Supportworks Server > Supportworks Server Configuration.)
+
*  For a service desk with a high volume of calls that have multiple  updates totalling more than 2000 words, the difference is likely to   increase again.
−
# On the Server tab, stop all services.
 
−
# From the MS Windows Control Panel, stop the SwSQLServer service.
 
−
#  Make a copy of the corrupt .MYI file and its associated .MYD file.  (Found in C:\Program Files\Hornbill\Core Services\SwSqlServer\data\''database''.)
 
−
# Restart the SwSQLServer service.
 
−
# Run the Interactive SQL program. (Start > Programs > Hornbill Core Services > Interactive SQL.)
 
−
# Enter the following, replacing <code>''database''</code> and <code>''table-name''</code> with the appropriate values.
 
−
#: <code>use ''database''; </code>
 
−
#: <code>repair table ''table-name'';</code>
 
−
#  Take a copy of the results returned. The table should now have been  repaired. Please forward a zipped copy of all your log files (found in C: \Program Files\Hornbill\Supportworks Server\log\) to [email protected]. Once this has been received, we will try to identify the root cause of the failure.
 
−
# On the Server tab of the Server Configuration utility, restart all remaining services.
 
−
 
 
−
Should  you require any further information and/or assistance please feel free to contact Hornbill Support on [email protected] or call 0208  5828228.
 

Latest revision as of 08:58, 16 July 2015



Status: Published
Version: 1.1
Authors: HTL QA
Applies to: Supportworks ESP

Size Comparison of SwSQL with MS SQL

The chart below compares the amount of space various quantities of specific types of database content stored by Supportworks will occupy in SwSQL and MS SQL, respectively. The two size columns show the cumulative totals as you keep adding the content indicated. Note that the space taken up by transaction logs is not included, so that a like-for-like comparison can be given.

Database Content SwSQL Actual Size (KB) MS SQL Actual Size (KB)
Blank database 0 3145
Clean installation 232 3145
1 site record 236 3145
1 charge centre record 239 3145
1 customer record 248 3145
10 site records 249 3145
100 customer records 263 3145
1 logged call (1 record in opencall + updatedb with 1000 words) 303 3145
10 calls logged 395 3145
50 calls logged 754 3145
100 calls logged 1177 3145
200 calls logged 2155 4194
300 calls logged 3114 5242
400 calls logged 4044 6291
500 calls logged 4981 7340
600 calls logged 5912 8388
700 calls logged 6857 9437
800 calls logged 7777 10485
900 calls logged 8710 11534
1000 calls logged 9652 12582
1000 calls then updated with 1 update of 1000 words 18246 20971
1000 calls then updated with 2nd update of 1000 words 26756 30408
1000 calls then updated with 3rd update of 500 words 31152 38797

General Observations

  • For a service desk with a rapid turn-around of calls (and therefore a low to medium volume at any given time), the MS SQL database is substantially larger than the corresponding MySQL database.
  • For a service desk with a high volume of calls, with one or two updates being required on average, the difference reduces to about 10%-15%.
  • For a service desk with a high volume of calls that have multiple updates totalling more than 2000 words, the difference is likely to increase again.
Retrieved from ‘https://wiki.support-works.com/index.php?title=Size_Comparison_of_SwSQL_with_MS_SQL&oldid=590’