Jump to content
Xtreme .Net Talk

Recommended Posts

Posted

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

Posted

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)?

Posted

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?

Joe Mamma

Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.

Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.

Posted

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?

Posted
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.

Joe Mamma

Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.

Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.

  • *Experts*
Posted

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

"I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
Posted

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?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...