Hi,
I am using Stored Procedures on SQL SERVER 2005. It uses cursor to perform row by row operations of string searching. Now the performance of the cursor is fine till the records to be searched are below hundread as the records in which i am searching is 1.5 lakhs. As one of the fields that i search in a bigger field rises to 7,500 unique entries the peroformance drastically decreases.
I know cursors can decrease performance but i have tried the iterative ideology too in it but still the performance is still the same.
The string searching is being done with the help of PATINDEX . It is my guess that it can be potential reason of not getting the desired performance as Patindex is considered to be a slow searching function.
Can anyone suggest any better string searching Method so that the performance may increase.
Help would be really appreciated.
Problem With Stored Procedure
sthangad
Hello....
It sounds like you need to think about extracting the block information out of the Address field. Its the only way that will allow you to increase the performance. If you need to "check" 7500 blocks in all 150.000 Records this means you will have to do 1125000000 calls to patindex. Now if you rewrite your procedure to only extract the Block information ONCE and then use an indexed field for futher opperations you can greately reduce the work the server hast to do.
I would suggest you add a new field to the table (or another table that can be joined with yours) and insert the extracted information into it. Depending on your DB design this could be done with triggers (If you dont use SP to access the Table) or better modify your SP that are responsible for CRUD to update this new table automatically when its updated,created or deleted. After creating this table or field you will be able to querry the Data with the speed you need. (Or modify you script to update your helper table shortly before it will run)
This tecnique will allow you to remember the results of 150.000 patindex opperations and access them with an index. So you wont call Patindex more times then you actually need to.
P.s.: Allwas try to avoid to store more then one information inside one field in an SQL Server. This will usually not work well and cause a whole lot of problem
Hope this helps
veeran
Hi Hatzi74,
your advice of extracting the block information and then using patindex would be viable if i have a data that is going to remain fixed.But in my case every month i will recieve new data ,the table and the fields would be the same but data might vary.
If i am wrong pls be kind to further explain me your solution. I would be grateful.
Jani Holopainen
Hi...
From your description you are doing it the following way (correct me if I am wrong) :
For each Block in the 7500
next
This could be rewritten to:
for each address in 150.000
next
for each Block in 7500
next
Yes its ugly pseudo code but I hope this will explain what i am talking about. Your goal is to seperate the nested loops from each other, so you can reduce the text analysis to 1 call per row in the 150.000... If you also add an index to the tmpTable then you should get some amazing performance benefits if what I described here is your current setup.
The construction of the tmpTable should only take a second or so.. So monthly changing Data is no Problem.
JasonSacks
hello vikram,
i am new to sql server. i am learning it gradually.i have come across a problem.can you please let me know how do you use cursors in stored procedure for row by row operations i am sending you my data design.
DATE : DEBIT : CREDIT : BALANCE
---------- ------------ ------------- -----------------
The balance column is filled by reduction of debit column from credit column.
Now I want the balance column to be filled by the same formula but the balance in the previous row should be added to the balance of the next row.How can I fill each row of the balance column like this
please help me out.
Erasmus
No, the DDL is the data definition language, basically the script of the table:
create table tablename
(
column1 datatype primary key,
etc. As much as you can include the better (as long as it is nothing proprietary cause this is open viewing :)
Lukasz Magdziarz
The main table has :
create table HERT(
surname nvarchar(200),
forename nvarchar(100),
postcode nvarchar(50),
Adressline_merged nvarchar(600),
townland nvarchar(100))
The other table which has the townland entries has
Create table city(
townland nvarchar(100)
)
the city table has 7,500 distinct records for townland and the hert table has 1.5 lakhs records.
i have to searh in hert.Adressline_merged a string i.e city.Townland and then if the string is found in the field hert.Adressline_merged the update hert.townland with the found string..
kindly help.
wintermi
No it is 150,000 rows .
Secondly i have a big address field which are 150,000 in number. Now the address field should have four fields which constitute the address like street name,block etc . Now if i have 7,500 different blocks then i have to search every block in each of the address field which are 150,000 in number. In the address field there is no gaurantee where this block might occur, i.e may be at the begining of the string or might be in the middle or in the end. So the whole string has to be searched.
If suppose the block is found then there is another column named as block which is updated by the successful search block to identify that the address field has this block name in it.
I have tried both cursor and iterative ideology on this but both seem to take the same time.
Simon Jefferies
>>1.5 lakhs
Is this like 1500 rows
what are you trying to search for, have you tried using LIKE
Denis the SQL Menace
http://sqlservercode.blogspot.com/dat1
Full text search has not helped i have tried that too..
Are you refering to description of the tables
Michael Wong
Can you show the DDl of the table(s)
Look into fulltext indexing or normalize your data
Denis the SQL Menace
http://sqlservercode.blogspot.com/