Time out while i fetch data from SQL in Asp.net MVC

Question

I hava a database which only have 2 table Object , User ,

Obviously user table has information about all user and the object table have millions of records

ObjectTable

Id (int)
Text1 (nvarchar(max))
Text2 (nvarchar(max))

I am trying to make a translator , first of all i put all data in database like following

1 : Good , Well
2 : Bad , NotWell
3 : Man , Husband Of Women

if suppose i havae 2 text boxes in my site user enter following text

Good Bad Man

then i will split that string on space and then i will have an array of string now i will took first element of array and go to server to find that wheather there is any match of Good in my database if i found match then i will replace that value with that Text2 like we have Well for Good which took too much time to translate and sometime it gives Request Timed Out . So , what is the best way to deal with it

The exception raised with the call stack would help us help you — gideon
– gideon, Commented Dec 13, 2012 at 8:26

jbl · Accepted Answer · 2012-12-13 09:01:03Z

1

You do not provide a lot of information to help you about your timeout problem.

Only things I can tell you are :

first of all, check that there are indexes on your Text1 and Text2 columns. If not, add it
look at your sql query. It should be SELECT * FROM OBJECT WHERE TEXT1='Good' (maybe add a top 100 to avoid returning to many things with LIKE ). For the index to run smoothly, there should be no function called on column TEXT1 (like uppercase or trim). If you use a like, it should be TEXT1 LIKE 'good%' (% at the end)
if you use a like, beware of not returning your whole table on a empty entry, % or _ entry ( LIKE '%', LIKE '%%', LIKE '_%' can be bad things)
why use nvarchar(max) if you are just storing words ?
don't forget to sanitize your entries to avoid sql injection

answered Dec 13, 2012 at 9:01

jbl

15.5k3 gold badges37 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Time out while i fetch data from SQL in Asp.net MVC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related