SQL Server – Update and Insert Statement Taking Forever

insertsql serverupdate

I have a database with two columns that is called using the following query:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC

In my application it is taking a ridiculous amount of time to call this statement

PredTable = PredTA.GetDataByPartialWord(combo)
            If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
            If PredTable.Count = 0 Then PredTA.Insert(combo, 1)

Indexing does not help because it is only a two column database and is constantly being updated

Is there any other way that I can improve the speed of this statement?

Best Answer

Thanks for updating that you are using Microsoft SQL Server, which does support Full Text Indexing. (As do many other versions of SQL servers.)

First of all your query is using a WHERE (Word LIKE @partialWord) which means that if there is a prefix wildcard (e.g. '%tion') the whole table has to be scanned and the contents of the Word column likewise scanned.

That is the base cost of this query.

Therefore, you need to investigate some other method of finding the partial words. For example, full text indexing (if available to you) may give you other options to use when finding %partial% words.

Create a full text index on the Predictions table for the Word column. This can be done through SSMS or by a script. This will populate the keywords table in the FTI catalog tables. Example from: http://msdn.microsoft.com/en-us/library/ms187317.aspx

CREATE UNIQUE INDEX pk_PredictionsID ON dbo.Predictions(PredictionsID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Predictions(Word) 
   KEY INDEX pk_PredictionsID 
   WITH STOPLIST = OFF;

Then here is a sample script that would help you to then find all the rows with wildcards.

DECLARE @SearchText NVARCHAR(2000)
SET @SearchText = ''

DECLARE @Pattern NVARCHAR(100)
SET @Pattern = '%colat%'

SELECT @SearchText = @SearchText + display_term + ' OR ' 
FROM sys.dm_fts_index_keywords ( DB_ID('DBName'),
    object_id('Predictions') )
   WHERE display_term like @Pattern

SELECT @SearchText = LEFT (@SearchText, LEN(@SearchText) - 3)
-- Contents "chocolate OR percolator" in my test case

SELECT * FROM Predictions
WHERE CONTAINS(*, @SearchText)

By building a full text query string that includes all strings containing the pattern joined by the OR operator, it allows Full Text Search to then directly access the rows that contain one (or more) of the words. This should be a lot faster than scanning the table.

SUGGESTIONS

Increase the size of your Log Buffer. The default value for innodb_log_buffer_size is 8M. You should make 256M or 512M. (Restart Required)
Turn off the Double Write Buffer. Set innodb_doublewrite to 0. (Restart Required)
OPTIONAL : Temporarily disable change buffering, which throttles secondary index updates.
OPTIONAL : In your application, you should limit the number of rows to insert. Try insert 500 or 1000 rows at a time. If the rows have long VARCHAR columns, then insert 100 or 200 rows at a time.

SUMMARY

Add these lines to my.cnf

[mysqld]
innodb_log_buffer_size = 256M
innodb_doublewrite = 0

then, restart mysql.

If these do not improve things, then try disabling the change buffering with

SET GLOBAL innodb_change_buffering = 'none';

You could always change it back to 'all' after a number of large bulk inserts.

GIVE IT A TRY !!!

Sql-server – SQL Insert Statement Persists Two Records When I Need Just One

From what I can see you have two problems. The first is the doubling. At a guess it is because of the SELECT statement in your INSERT statement.

       SELECT @NewMetricID, 
            NULL, 
            sm.sortorder, 
            ISNULL(sm.MetricOrder, 1), 
            ISNULL(sm.CategoryOrder, 1), 
            sm.RptCurrentGroup, 
            'System', 
            GetDate(), 
            NULL, 
            NULL, 
            'N', 
            NULL,
            NULL
        FROM Metric_Instance mi 
        INNER JOIN Shared_Metrics sm on mi.MetricID = sm.MetricID
        WHERE mi.MetricID = @CurrentMetricID 
            AND mi.MetricDisposition <> 'Suspended' 
            AND mi.isDeleted <> 'Y'
            AND sm.isDeleted <> 'Y';

I would be willing to bet your query is returning more than one row. Any time you have a join like that you run a risk of creating duplicate rows. The easy way to fix it is to do this:

       SELECT DISTINCT @NewMetricID, 
            NULL, 
            sm.sortorder, 
            ISNULL(sm.MetricOrder, 1), 
            ...
            ...
            ...

The only real change is that that I'm putting a DISTINCT at the top of the query. This means that if there are any VALID duplicates you are still going to pick them up. You are not actually referencing any columns from Metric_Instance so you could change it from a JOIN to a WHERE EXISTS (SELECT 1 ...

On to problem two. In your code you are using scope_identity(). This is only going to return the last identity value created. If you insert more than one row the previous rows are going to be missed. The best solution here is to get rid of the cursor and change this into a batch process. INSERT all possible rows initially using the OUTPUT clause to dump a list of identity values and CurrentMetricIDs into a temp table. Then do your update using that temp table to update all of the values at once. Not only will this fix your problem but will probably run quite a bit faster as well.

Best Answer

Related Solutions

Mysql – Optimizing a large number of INSERT … SELECT statements

SUGGESTIONS

SUMMARY

GIVE IT A TRY !!!

Sql-server – SQL Insert Statement Persists Two Records When I Need Just One

Related Question