how to grab first 20 words from the database 1

[flyclassic22]

I've a database table with columns Description.
However i would only like to grab the first 20 words from the field, how do i go about doing it in ASP.NET with MSSQL ?

Any idea, algorithmn to share?

 

[ca8msm]

You could use the Split method and take the first 20 items. See the example here.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

i would suggest doing this in MSSQL itself using UDFs...

Known is handfull, Unknown is worldfull

 

[ca8msm]

We've already been down that route and it's probably not a good choice (thread183-1352789).


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

wow,

my suggestion was from another angle, if this field is a varchar field, then ALL the words will be returned, then the split will happen. therefore extra data is returned to ASP.NET...

Known is handfull, Unknown is worldfull

 

[ca8msm]

Yes, but we don't know what the user wants to do with the data. For example, they may only be displaying a subset to the user and if they click a "read more" link it may display the full text.

Doing the split functions in .NET will most likely be faster anyway as you are using built-in methods. In SQL Server, those methods don't exist and it would involve looping through the string one character at a time and so performance would be worse.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

>>In SQL Server, those methods don't exist and it would involve looping through the string one character at a time and so performance would be worse.

doesnt the same thing happen in .NET side too (assuming the user shows ALL records without pagination).

and one more thing, i have heard that you can write .NET code in SQL Server 2005, so maybe that is also an option...

Known is handfull, Unknown is worldfull

 

[ca8msm]

doesnt the same thing happen in .NET side too (assuming the user shows ALL records without pagination).

Not if you use the method I suggested. My example uses the Split method rather than looping through one character at a time which should be faster.

i have heard that you can write .NET code in SQL Server 2005, so maybe that is also an option...

Yes, you would use CLR but I imagine those in the SQL Server forums will have have more experience on whether that would be an option performance wise or not. My guess is not.



____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[flyclassic22]

okay, ill be trying out, how do i bind it to a GridView In after grabbing 20 words? Coz i understand GridView is Datasource , need to bind data.

 

[ca8msm]

You don't bind it afterwards. Bind the GridView as normal and call the function for each relevant cell/control in the RowDataBound event.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

>>Not if you use the method I suggested. My example uses the Split method rather than looping through one character at a time which should be faster

that was exactly my doubt. split itself is a method right? it can be that this method itself goes through each char before finding where to split?

are there any ready made results available that states that a split in ASP.NET is faster than SQL Server? or on what basis do you think it could be faster?



>>Yes, you would use CLR but I imagine those in the SQL Server forums will have have more experience on whether that would be an option performance wise or not. My guess is not.

why? CLR again uses only inbuilt .NET functions right? the problems are with inbuilt T-SQL functions...

Known is handfull, Unknown is worldfull

 

[ca8msm]

that was exactly my doubt. split itself is a method right? it can be that this method itself goes through each char before finding where to split?

are there any ready made results available that states that a split in ASP.NET is faster than SQL Server? or on what basis do you think it could be faster?

Yes, the Split method will no doubt use some internal method of finding out where the spaces are but that is the point. As it is an internal method of the framework, the Split method itself will have been produced and compiled into a much faster method than you will be able to reproduce by simply iterating through each character. If you want to see for yourself, try a simple example and time both methods to see which comes out faster.

why? CLR again uses only inbuilt .NET functions right? the problems are with inbuilt T-SQL functions...

Yes, it does but it executes them against a database. As I said above, you're better off asking the experts in the SQL Server forum whether they think CLR on a database is a good idea or not and whether it can have any performance implications. I'm guessing that they would say it would be better off doing this on the application itself rather than on the database (as would seem to be the case if you read the original thread).


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

>>Yes, the Split method will no doubt use some internal method of finding out where the spaces are but that is the point. As it is an internal method of the framework...

hmm, point. i will check that.

but i have another point to raise in this context, someone in my organisation made a point:

"the looping written in datagrid will ALWAYS be faster than the looping that we could manually using custom code for the same set of records"

this leads to one important question:
Are inbuilt CLR functions NOT written in .NET? Are they in C / C++?
If not then how come they are fast? Is it because of the language they are written in or do they follow a algorithm that makes the faster?
In short how do we make our functions as fast as inbuilt functions?

Known is handfull, Unknown is worldfull

 

[ca8msm]

Are inbuilt CLR functions NOT written in .NET? Are they in C / C++?

They will be built in whatever the .NET framework is buit in. I don't know for sure but I imagine it will be some sort of low-level assembly code.

As we don't write to this level and instead use the wrapper that is the framework, I assume we will never be able to write methods that will be as fast as the low-level code.

This is all based on my assumptions though, don't take that as concrete evidence.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

>>As we don't write to this level and instead use the wrapper that is the framework, I assume we will never be able to write methods that will be as fast as the low-level code.

hmm,

thanks for your comments mate...

Known is handfull, Unknown is worldfull

 

[AlexCuse]

Hi Guys,

Couldn't help dipping my toe in these waters.

CLR Functions are (when used appropriately) faster because T-SQL is designed to perform set-based operations, NOT looping and math. It can do both adequately, but not necessarily all that well (compared to alternatives).
The .net framework's improved speed with looping, string manipulation and math calculations is what gives such an advantage to CLR functions. There seems to be an overhead involved with using them, but from what I've seen this is overcome at about 10,000 iterations of the function (tested with moderately complex math calculations). With < 10,000 iterations, the speed lost is negligible.

In wilsonfuqi's case, I would think the speed gained would be tremendous (over SQL Server methods anyway) because of how S-L-O-W looping is in SQL. However, some web hosts (well, mine anyway ;-) ) will not allow CLR to be enabled on their server.

If this is an option, it should probably be pursued in order to limit what is sent back from the database, but if it is not I would think that trimming the words off in ASP.net is far superior.

I would imagine that how CLR functions are written is irrelevant, because no matter the language they are compiled to the same framework.

Hope this clears things up a bit,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[vbkris]

>>I would imagine that how CLR functions are written is irrelevant, because no matter the language they are compiled to the same framework.

confused, do you mean the inbuilt functions are also written using VB.NET / C#.NET etc? if they are then how come they are faster than custom functions (e.g.: the looping example that i qouted)...

Known is handfull, Unknown is worldfull

 

[AlexCuse]

I do not know that they are faster, but they would provide an advantage by limiting what is sent over the network to only the 20 words rather than entire column which could contain thousands. This is where they provide an advantage (allowing you to use your .net functions within the database context rather than bringing large recordset across to run functions on in your application).

What I meant is that they are faster than their T-SQL equivalents.

Ignorance of certain subjects is a great part of wisdom