getting the number of rows...

[yaniv]

My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:

 

"In the forums there is X results for the word X

In the articles there is X results...."

 

And when the user click one of those lines, the list shows the specific results in that section.

 

My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows�

 

Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)

 

Great thanks

[samsmithnz]

do you mean count?

 

select count(*) from myTable

[yaniv]

does count will give me the number of the times that the string is in the table?

 

other words, can you use:

select count(*) from myTable WHERE text LIKE%(string)?

[Joe Mamma]

the syntax is

 

'Select count(*) from MyTable where MyValue Like 'xyz%'

or

'Select count(*) from MyTable where MyValue Like '%xyz'

or

'Select count(*) from MyTable where MyValue Like '%xyz%'

 

but keep in mind, 'Like' will not use any indexes and is processor heavy

 

is this a web site???

what is the communication architecture between DB and App Data Layer of your app?

[yaniv]

it is a web site, I use ADO.NET architecture for sql server.

 

some thing like

 

Public oConnection As New SqlConnection("Data Source=SHORESH;Password=qwerty;Persist Security Info=True;User ID=ShoreshDBUser;Initial Catalog=ShoreshDataBases")

 

Do you have another idea instead of LIKE?

[samsmithnz]

No like is what you want. So if you're quering for people

 

select count(*) from people where first_name like 'sam%'

[Joe Mamma]

Do you have another idea instead of LIKE?

Not really. I just avoid it whereever possible.

 

Is the SQL server on the same machine as the webserver?

 

Using a paged datagrid?

I believe the dataset has to be completely pulled across to calculate the current page.

 

I generally use a function based on DataTable.Rows.Count, currentpage, grid.Rowcount to return a string 'Records x-y of N'

 

Its a trade off of processor performance and data throughput as far as I can tell.

[Nerseus]

I believe most databases will use an index if the "LIKE" expression does not start with a wildcard. It's always up the optimizer of the DB engine for final decision though.

 

It's generally preferrable, and quite normal, to prevent a user from doing a like search that starts with a wildcard.

 

If you're searching a LOT of text and each piece is a large string, you might need to develop some kind of keyword indexing that exists separately from the "body" of text that you want to return. For example, a separate table that contains only keywords and another link table that links to matching records.

 

As for showing paged results, you generally have two options: return the full dataset on every call and only display records 1 thru 10, 11 thru 20, etc. OR use some kind of persistent cursor or dataset. If this is a web app then you limit your options a bit.

 

You might want to check out the "starter kits" available through http://www.asp.net.

 

-ner

[yaniv]

what this "count" query returns?

 

i trued this:

 

cmd.commandtext = "SELECT COUNT(*) from tbl_Posts WHERE keywords like '%string%'"

 

dim reader as sqldatareader = cmd.executereader

 

dim i as int16 = reader.read

 

and i get allways 1....

 

how should i get the number?