Optimizing SQL Database

[edgare]

Anyone have experience in optimizing the database?
Would you just use the tools in SQL maintenance to resize freespace ... reindex ... or have you guys tried something more? I came across a vendor

http://www.quickshift.com

they claim to at least double the speed. I am going to try this and will report back weather or not it worked. If anyone has tips on database optimization other then sw/hw bottlenecks ... but something like rebuilding the indexes .... Macola suggested that we archive the old data we have 6 yrs of stuff in the database but our controller is dead against it.

 

[mksnyder]

Are you doing ANY optimizations at all right now? Any Maintenance Plans scheduled on your server? I would be wary of claims of 'double the speed'. You can get soaked for a lot of money and not get any better results than what you can do yourself.

I'm happy to pass on some scripts of what we are doing, and point you to a couple of websites that may be helpful.

Contact me by return email and we'll talk.

 

[edgare]

Thanks couldnt find ur email on here
but you can email me edgare@impress1.com.

Thanks

 

[AKPolarBear]

Might help to post your SQL version and server info.

Sometime ago in another post elsewhere in this forum, I found a Macola user that was doing transaction log backups every hour. I thought that was a lot until we ran into a disk space problem. We have several Macola DB's and I ended up moving some of the less used ones to another drive but it was tweaking the original settings (set up by the VAR) in the various SQL maintenance plans that made the big difference. I started testing various times for the transaction log backups and now also do mine every hour from about 7am to 7pm. It has helped a whole lot with space issues and speed. Except for the first TRX backup of the day, most take only 1 to 4 seconds to complete and the users never know it. I also do DB maint., Integrity checks and Optimizations once per day during off hours. These may be apparent to those who are SQL admin. (I'm not) but they are not in the Macola set up instructions. There are more to the plans then just timing the operations but this will give you an idea where to start. It is also important to get them done in the right sequence.

2X the speed from other software? I doubt it. Start with what you already have to work with.

 

[edgare]

hey can someone finetune this sql code to work with macola Sorry its rather long....

/*
Defrag or re-index all indecies
*/

DECLARE @TableName sysname
DECLARE @IndexName VARCHAR(100)
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT name from sysobjects where xtype='U'

OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid,name FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid,@IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Maintenancing ' + (@TableName) + '.' + (@IndexName)
--IF @indid <> 255 DBCC INDEXDEFRAG (0, @TableName, @indid)
IF @indid <> 255 DBCC DBREINDEX (@TableName, @IndexName)
FETCH NEXT FROM cur_indfetch INTO @indid,@IndexName
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

 

[keain]

Before I rebuilt the indexes, I'd check the indexes on the Macola tables first. You can do this by downloading the mslsyscheck from the Infomine section and running it. This utility will tell you which tables are missing indexes. If it's only a few, you can rebuild them through system manager\processes\rebuild x.

The utility is attached to a document entitled "Progression SQL and Performance Issues." I don't have access to the portal, so I can't look up the doc number for you.

 

[abaldwin]

Software that doubles the speed of SQL....MMMMMMMMM.........

I can only think of one way to double the speed and processing power of the SQL server.

If you want to send me the money you would spend on their software I will tell you the few guaranteed ways to speed your performance of SQL and macola.



Acatually I guess I will tell you here for free.
Most macola users buy the bottom of the line (just meets specs) server to run SQL on.

1. Get more processors and more ram
Single processor with 512 meg you need to upgrade to multiprocessor with 2 gig.

2. Get more processors and more ram

3. Get a different ERP system.
Below is a quote from QuickShift web site. (By the way they do not have a price for the software listed .... Hinky)

"The testing I performed was artificial and you will most likely not see the kind of performance numbers you see here in the real world. According to testing performed by Quickshift, real world SQL Server performance boosts should be more in line of 200% to 300%, which is still very substantial. The amount of performance boost depends on a lot of variables, including:

* Your hardware (especially how much available RAM you have, the speed of your disk subsystem, and current CPU levels)
* Your application (how it is designed, coded, and used.)

The best way to find out for yourself how much of a boost Quickshift for SQL Server will give you is to try it. Quickshift offers a free trial of the software, so you can find out for yourself exactly what kind of performance boost you can expect."

In other words.....They know that most of the SQL users out there are using substandard servers.

GET MORE PROCESSORS AND MORE RAM. Forget the software. This is like the old Ram compressors of the DOS Days.

Second point from the quote. You have no controll over badly written applications. Macola is not written for SQL Server. Macola did not utilize many functions in SQL that would make their software BLAZING fast. Instead they rely heavily on the local machine for processing. Until Macola changes this they will never get the speed they could.

Laterdays






Andy Baldwin

 

[abaldwin]

One more note that I failed to mention.

Applications such as Crystal reports that are optimized for SQL run Blazingly fast on SQL compared to the same data on BTRIEVE. This alone should tell you something about the Macola application. Proof is in the pudding. Macola itself runs very fast on Btrieve (OKAY Pervasive) but the crystal reports run slower. Put the same data under SQL and it flip-flops like Kerry on election day.

GET MORE PROCESSORS AND MORE RAM. Never spend the minimum on the server that is the core of your business.

Also, don't run every other file and print sharing needs from teh macola server. Let SQL be SQL.

Okay enough for now.

Andy

Andy Baldwin

 

[AKPolarBear]

That may not solve everything but is certainty supported by M$ and other SQL experts and I too would never build to the Macola minimum. Our databases size is between 6 - 8 gigs.
We have a new dedicated SQL server with dual processor Xeons, 4 gigs of Ram, raid 5 and spare drive for seldom used databases and Macola still does not run 'blazingly" fast. I have Crystal reports that we have to leave running at night because they take 3 - 4 hours to complete (better then before at 4 or 5 hours with same SQL software) and the MRP Regen is also done after hours because it takes 3 - 4 hours.
Macoal is not optimized for SQL.

 

[dgillz]

Bob,

I would like to see the record selection on those 3-4 hour crytal reports. I will be we can speed this up dramatically with some quality time spent on this. In particular, when you click on database, show SQL query, make sure that every data element of the record selection formula is getting translated to a SQL WHERE clause. If its not we can most likely tweak it so that it will. And then you'll see a fast report.

For one customer, I took a report that was taking 2 hours to run, tweaked to record selection so that is translated to a SQL WHERE clause, and it now takes 10 seconds.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[AKPolarBear]

The particular report came from our former(sold out twice since then)VAR as it was beyond my limited CRW skills.

But the Regen is a Macola issue.

 

[dgillz]

There is little you can do about the regen, however I had a customer (using Pervasive) once rebuild every single file that MRP touches, and his regen went form 9 hours to 31 minutes.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[abaldwin]

I agree. I would like to see the crystal report that takes that long as well. My live database is 14 gig and none of my crystals take more than a few minutes to run.

Andy

Andy Baldwin

 

[AKPolarBear]

rebuild every single file that MRP touches, and his regen went from 9 hours to 31 minutes."

How long did it take to rebuild all those files and was that needed to be done every time you had to regen? We came from Pervasive and it ran a little slower than now. And this is still needs to be run as a single user function which makes it even more inconvenient.

I think we got a little bit "snaked" by the upgrade to SQL pitch and as Andy said, it won't get any better until Macola re-writes the code to take advantage of SQL.

We also don't use local processing as we are Citrix based and run Macola off 2 dedicated servers and all the traffic must pass through the Metaframe server, which is now hammered a lot during the day. Solution there is to add another Citrix server or 2. That bottleneck might have an impact in my particular processing times during the day but certainly does not after normal hours and before the nightly maintenance routines and back up start. However, certain Macoal functions can slow everything down over time during the day. I.E. when users leave the OE screen open while waiting to start the next entry. If left open and unused, I can see the Citrix CPU load increase over time for it. After about 15 minutes, it is having an overall bad effect. I kill the process and the load immediately drops from high 90's to lower 30's or so. There are many such screens that cause this problem and SQL might be wrongfully blamed. Again, this may be only applicable for my Citrix based situation.

 

[dgillz]

Bob,

This was a one time rebuild. He also ran the file validation reports on the IMINVLOC, IMITMIDX and BMPRDSTR and corrected every single error returned. the BMPRDSTR in particular can slow down a regen.

In terms of how long it took to rebuld the files, it took several days. He launched rebuilds from several machines as he was walking out the door at 5pm every night. After all the files were rebuilt, the performance was that much better.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz