Geek asks and answers

Site performance decreased after changing varchar to nvarchar

I recently had to convert one of a table column definition to nvarcharfrom varchar. Since then, I feel like finding data on a table has become slower.

I have 4,200,000+ rows in a table and growing.

My web application does not currently use stored procedures to retrieve data from the database. If I use a stored procedure, will that improve search performance a bit?

Or is there any other advice you would give for improvement?

Here is the query currently in use:

SELECT TOP 100 id, callerID, dateTime, activity, senderNum, msgSent, smsgRespond, msgIn 
FROM tbl_activitylog 
WHERE callerID = @callerID 
ORDER BY id DESC

A column msgSentis one that has been converted to nvarchar.

Below is the table structure:

id (int, Primary Key, Auto Increment)  
callerID (bigint)  
dateTime (datetime)  
activity (varchar(50)  
senderNum (int)  
msgSent (nvarchar(160))  
smsgRespond (varchar(50))  
msgIn (varchar(160))

I do not understand part of the index.

+3
3

, , , .

- INDEXES.

index (callerID, id DESC).

, .

SSMS " ", , , . . , , .

+4

: . , , , " SELECT", .

.

DBCC CHECKDB('<db_name>') -- check the db for error
DBCC UPDATEUSAGE('<db_name>') -- fix errors

!

: : CalledId.

SQL SELECTS, , columsn WHERE . !

+1

varchar, Nvarhchar, . ( ) , :

ALTER INDEX IX_msgSent ON tbl_activitylog REBUILD
+1

More articles:


All Articles