Search is too slow? Why?

  • Thread starter Thread starter mp
  • Start date Start date
M

mp

Hi,

MS Access DB, C#, VS, SQL

I have implemented search with SQL statements like follows:
SQLString = "SELECT ENGLISH FROM MyTable WHERE ENGLISH LIKE
'"+txtWordManipulation.Text+"%' ORDER BY ENGLISH";

end everything is desperately slow. DB is more than 150k words and I haven't
defined primary key because indexed option is set on Yes (Duplicates OK).

Do you have some suggestions or idea for different search?

Thanks
 
First of all, I haven't used Access very much, so I'm not sure what the
limitations are. I would expect that doing a LIKE with the "%" on the right
would allow the index to be used and shouldn't be too slow. On the other
hand, 150K records sounds like a lot for an Access database.

You might want to figure out some sort of alternate indexing scheme. For
example, you could make a table that contains the first two letters of the
"ENGLISH" column with an index into MyTable for matching records. Depending
on how large the rows are in MyTable, scanning the index table may be much
faster.

Assuming MyTable look something like this:

MyTable
- int - MyTableID
- char(255) - ENGLISH
- other fields follow -

you could do:

MyIndexTable
- int - MyTableID
- char(2) - MyTableStart


the MyTableStart field would simply contain the first two characters of the
ENGLISH field from MyTable. You could do a select like:


string shortString = string.empty;
if (txtWordManipulation.Text.Length > 1)
{
shortString = txtWordManipulation.Text.Substring(0,2);
}
else
{
shortString = txtWordManipulation.Text[0].ToString();
}

SqlString = "SELECT ENGLISH FROM MyTable WHERE ENGLISH LIKE
'"+txtWordManipulation.Text+"%' and MyTableID in (SELECT MyTableID FROM
MyIndexTable Where MyTableStart = '" + shortString + "')";

I honestly don't know if this would be faster. You'd need to test it, but
the subquery should restrict the search to a much smaller subset of MyTable
and may result in a significant increase in speed.

There are a number of variables that can affect this, however.

If the rows of MyTable are not particularly large, then this technique
doesn't help you much. Also, this technique will slow down inserts, updates,
and deletes as you'll have to update two tables.

Also, it's likely the records in MyTable are spread out across the table,
requiring most of the database to be read in anyway.

Again, I don't know Access very well, so I don't know how it handles the
data internally, but it may be possible that if you generate the table by
inserting the records in alphabetical order (such that words that have
similar starting strings are close together), that it may be faster as well,
but this depends on whether access keeps the records physically in the order
that they're inserted. I don't know that it does this.

Anyway, hope this gives you some possibilities.

Pete
 
Can you wquantify your complaints?

*What is slow in your eyes?
*If the access database on a network share?
*What is the content of txtWordManipulation.Text
*What is the table structure, plus all defined indices.

Btw:
*You do know you allow me to delete your complete database and run any SQL
statement I want with this stupid code of yours? SQL Injection attack, plain
and simple.



--
Regards

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
(CTO PowerNodes Ltd.)
 
Btw:
*You do know you allow me to delete your complete database and run any SQL
statement I want with this stupid code of yours? SQL Injection attack, plain
and simple.
Click to expand...

You must be one of the most exceptional programmers in the world to be so
confident that your own code is completely without error. I don't think it's
any help to call someone's code "stupid". Nothing wrong in pointing out
mistakes, but some of us come here to help and learn. Not be insulted.
 
You must be one of the most exceptional programmers in the world to be so
confident that your own code is completely without error. I don't think it's
any help to call someone's code "stupid". Nothing wrong in pointing out
mistakes, but some of us come here to help and learn. Not be insulted.
Click to expand...

It is no insult.

This is a beginner 101 error.

Basically, if you do not know what a SQL Injection attack is, you should not
be allowed near code that runs against a SQL database.

This is like a doctor not knowing that people may die when loposing too much
blood.

It is BASIC.

Everyone does make errors, but a SQL Injection attack possibility by SUCH
code deserves to be called stupid code.

--
Regards

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
(CTO PowerNodes Ltd.)
 
Thanks for your opinion about my code. You are right about possibilities of
attack, but decent men should suggest that on different way. Your approach
is not professional, especially for person from consultants company.

I'll avoid possibility of this attack later with parameterized queries and
user inputs validations.

Anyway, I am not able to see connection between possibilities of SQL
Injection attack and my question???

I am sorry but I never heard for you. I would like to believe, you a
brilliant mind and extraordinary programmer.

I am sure you have solution for this problem. Do you? May be not, may be you
are one ordinary man and average programmer. But do not worry, here is a lot
of folks to help you.

Please keep in sight and do not forget, this place is home of beginners and
extraordinary professionals who helps them.

Bye,
Miro
 
mp said:
Thanks for your opinion about my code. You are right about possibilities of
attack, but decent men should suggest that on different way. Your approach
is not professional, especially for person from consultants company.
Click to expand...

Well, so far I have made it part of my life not to lie. I see a thief, I
call him a tihief. I see idiotic code, I call it idiotic code.

I'll avoid possibility of this attack later with parameterized queries and
user inputs validations.
Click to expand...

Technically all you need to do is your homework with especial char handling.
If you are doing some little replaces on the iunput string, you are fine.
Anyway, I am not able to see connection between possibilities of SQL
Injection attack and my question???
Click to expand...

This is totally your problem, because...

....in my answer I asked some questions about your code, and also made a
comment about your openness for SQL Injection.

You cared to ignore my answers and focus on the comment of the code.

So, what do you expect?

You did not provide enough information for sensible help, and have not
answered my questions yet. Without he answers I do not feel comfortable to
answer.
I am sorry but I never heard for you. I would like to believe, you a
brilliant mind and extraordinary programmer.

I am sure you have solution for this problem. Do you? May be not, may be you
are one ordinary man and average programmer. But do not worry, here is a lot
of folks to help you.
Click to expand...

Spoken like someone who has no clue about SQL Injection attacks, and as
someone who - can not read. Like my answer and my questions to your problem.

I surely have a solution. IOnteresting point is, though, that your problem
technically SHOULD be a non-issue. This is why I asked questions.

That you tend to ignore them is basically one exact thing: your fault. Would
you have answered them, you would have been helped by now.
Please keep in sight and do not forget, this place is home of beginners and
extraordinary professionals who helps them.
Click to expand...

Well, they should start learning to read before trying to program.

Means: when you come asking for help, and are asked to provide more
information, you should provide it if you expect a sensible answer.


--
Regards

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
(CTO PowerNodes Ltd.)
 
It is no insult.

This is a beginner 101 error.

Basically, if you do not know what a SQL Injection attack is, you should not
be allowed near code that runs against a SQL database.

This is like a doctor not knowing that people may die when loposing too much
blood.

It is BASIC.

Everyone does make errors, but a SQL Injection attack possibility by SUCH
code deserves to be called stupid code.
Click to expand...

I don't like to but in here, and i try to avoid it in proffessional
newsgroups such as this.

I thought the origonal poster asked a fairly easy question, which had
no mention to SQL injection at all. Now fair enough that this could be
a problem, but you could use a little tact. I know of seasoned DB
programmers who had never heard of SQL injection until a recent
ADO.net course, so it's not just a beginners problem. Add to that, the
code the OP is providing is a snippet, you do not know what is going
on behind the scenes.

PS, for those other programmers, myself being a hardware engineer and
frequently visiting h/w NG's this thread is very tame, i can assure
you! ;) I do enjoy the professional behaviour on MS programming NG's,
it's nice to see.
 
Back
Top