Re: sql server comparing two huge tables - Database Discussion Boards

How to edit Dataset value (NOT DATA GRID)

sacr834-Aug-09 0:12

4-Aug-09 0:12

Hi All,

i want to edit DataSet value before binding to GridView. How can i do that..?

conn.open();

string result="select name,address,age";
sqlCommand cmnd=new sqlCommand(result);
sqlDataAdapter ad=new sqlDataAdapter(result,conn);
DataSet ds=new DataSet();
result.Connection=conn;
ad.SelectCommand=result;

// in here i want to edit (divide age value by 2) before bind data to gridview.

ad.fill(ds);
GridView1.DataSource=ds;
GridView1.DataBind();

conn.close();

i want to divide age value by 2 before bind data to gridview.How can i do that...???

Thank you very much .....

CheeN

Re: How to edit Dataset value (NOT DATA GRID)

Kschuler4-Aug-09 3:14

Kschuler

4-Aug-09 3:14

A DataSet object is a collection of DataTable objects. You could loop through the table and update the age with something like this:

For Each row As DataRow In ds.Tables(0).Rows<br />
   row("age") = row("age") / 2<br />
Next

Or you could just do it in the select by changing your select statement to this string:
"select name, address, age/2 as age"

Hope this helps.

Re: How to edit Dataset value (NOT DATA GRID)

sacr835-Aug-09 20:21

sacr83

5-Aug-09 20:21

Thank u 4 your post. I fixed my problem..Thank u all...

CheeN

Re: How to edit Dataset value (NOT DATA GRID)

Andy_L_J4-Aug-09 9:38

Andy_L_J

4-Aug-09 9:38

For Each dr As DataRow In ds.Tables(0).Rows

  dr("ColumnName") = (dr("ColumnName") / 2)

Next

Where ColumnName is your data column in the DataSet Table.

I don't speak Idiot - please talk slowly and clearly

I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury

Driven to the arms of Heineken by the wife

sql server comparing two huge tables

ps_prakash023-Aug-09 21:26

ps_prakash02

3-Aug-09 21:26

Hi Guys,

I'm in need of your help.

my problem is, i have two tables namely MASTER and LINECONTENT.

"MASTER" table contains 5,00,000 records,
The columns in MASTER table is
masterid int, masterdesc varchar(1000).

"LINECONTENT" table contains more than 1000 records &
its columns are pageno int, lineno int, content varchar(2000).

I want to compare each row of "MASTER" table, masterdesc column value with the "LINECONTENT" table content column,
If any records matched i want to take the matched master record and store it in another table.

for this i used the below query.

select b.* from linecontent a
join master b on a.content like '%'+b.masterdesc+'%'

but this query is taking much. more than 15 minutes
i want to decrease the time give me some suggestions handle this problem

thank you
regards,
prakash

Re: sql server comparing two huge tables

Mycroft Holmes3-Aug-09 23:18

Mycroft Holmes

3-Aug-09 23:18

Throw an index on MasterDesc

Try and use charindex in the where clause - it may not help, just an idea.

Never underestimate the power of human stupidity
RAH

Re: sql server comparing two huge tables

ps_prakash023-Aug-09 23:50

ps_prakash02

3-Aug-09 23:50

Hi,

Thanks for your reply.

sorry i forgot tell you that i have created nonclustered index in both tables. In "MASTER" i have created non-clustered index for masterdesc column similarly in "LINECONTENT" table i have created non-clustered index for "content" field. even though its taking time.

thanks & regards
P.Prakash

Re: sql server comparing two huge tables

David Skelly4-Aug-09 2:10

David Skelly

4-Aug-09 2:10

Putting an index on masterdesc will not make any difference to this query because you are using a leading wildcard: LIKE '%abcde%'. The query optimiser will not even try to use an index for this: it will just go through the rows one by one checking each in turn, because that's all it can do.

So basically the query will take every row in LINECONTENT and test it against every row in MASTER. That's why it's taking so long.

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 2:26

ps_prakash02

4-Aug-09 2:26

Hi,

but my functionality is like that. i want to compare "master" table with "linecontent" table and matching rows should be taken. so i have written like that but its taking nearly 15 minutes. i want to decrease the time to some 2 to 3 minutes. please tell me the solution

Thanks & Regards,
P.Prakash

Re: sql server comparing two huge tables

David Skelly4-Aug-09 6:20

David Skelly

4-Aug-09 6:20

I understand what you are trying to do. The problem is that a leading wildcard search is a performance killer, end of story. There is no quick fix or easy answer.

Depending on which version of SQL Server you are using, you may be able to use Full Text indexing. I can't remember whether that was added at SQL Server 2005 or SQL Server 2008 off the top of my head.

Otherwise, I would recommend looking at something like Lucene or Microsoft Search Server to see how they work, as they do something similar to what you are trying to achieve.

Re: sql server comparing two huge tables

Mycroft Holmes4-Aug-09 16:12

Mycroft Holmes

4-Aug-09 16:12

I wonder if he tested charindex and if it give a better performance. I would be surprised if it did but needs testing.

Never underestimate the power of human stupidity
RAH

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 17:44

ps_prakash02

4-Aug-09 17:44

Hi,

I could not understand what you have said.
Could me please explain me deeply.

Thanks & Regards,
P.Prakash

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 17:39

ps_prakash02

4-Aug-09 17:39

Hi,

thanks for your answer,
we are using sql server 2005 i'll look for those two options you have specified.

regards,
P.Prakash

Re: sql server comparing two huge tables

ps_prakash025-Aug-09 2:09

ps_prakash02

5-Aug-09 2:09

Hi David Skelly,

for this problem can we use table partition method. will it help me to solve the problem.

Thanks & Regards,
Prakash

Re: sql server comparing two huge tables

Blue_Boy3-Aug-09 23:24

Blue_Boy

3-Aug-09 23:24

Try this

select master.masterdesc <br />
from master ,linecontent<br />
where master.masterdesc = linecontent.content<br />

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 0:28

ps_prakash02

4-Aug-09 0:28

Hi,

Thanks for your reply.

i tried for your solution but still i'm having same performance

just give me some solutions

thanks & Regards
Prakash

Re: sql server comparing two huge tables

Blue_Boy4-Aug-09 4:46

Blue_Boy

4-Aug-09 4:46

Perhaps you should check this result.[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Re: sql server comparing two huge tables

ps_prakash024-Aug-09 21:52

ps_prakash02

4-Aug-09 21:52

Hi,

thank u i'm looking for it

regards
Prakash

How to check for a never populated table on sql server 2005

Roberto Mazzone3-Aug-09 6:34

Roberto Mazzone

3-Aug-09 6:34

I use IDENT_CURRENT to predict the next identity on a table (I know I have to be careful).
IDENT_CURRENT return the SEED value (1) for a never populated table
(it's a bug...https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=297781[^])
and the same value for an empty table with 1 rows inserted and deleted.
so when IDENT_CURRENT return 1 I don't know if the new identity value will be 1 or 2.
I have to exec:

DBCC CHECKIDENT ('table_name', RESEED, 1) --if a table was empty and never populated

DBCC CHECKIDENT ('table_name', RESEED, 0) --if a table was empty and just  populated

in this way IDENT_CURRENT will return always 1.

I'm searching for a more elegant solution than, for an empty table, insert a dummy row, get the IDENT_CURRENT and delete the dummy row!

Many thanks to all

Re: How to check for a never populated table on sql server 2005

David Mujica3-Aug-09 8:04

David Mujica

3-Aug-09 8:04

Why don't you use a "select count(*)" statement to determine if the table has any rows in it ? I must be missing something. Confused | :confused:

Re: How to check for a never populated table on sql server 2005

Roberto Mazzone3-Aug-09 20:00

Roberto Mazzone

3-Aug-09 20:00

Ok, I try to explain better.
I want to predict what will be the next identity value for a table.
If a table have 1 row inserted and deleted, the table is empty and populated.
I this case IDENT_CURRENT return 1 and the next id value will be 2. Correct.
If a table is empty and never populated IDENT_CURRENT return 1 again (it's a bug) and the next id value will be 1. In this particular circumstance i can't predict the next id value and i would like to kown a way to check if a table was populated or not.
"Select count(*)" don't solve the problem.
Anyway many thanks!

Re: How to check for a never populated table on sql server 2005

leckey3-Aug-09 8:54

leckey

3-Aug-09 8:54

If your identity field is called something like myIndex, why not just do something like

DECLARE myCounter INT
SET @myCounter = SELECT MAX(myIndex) FROM myTableName
DELETE FROM myTableName WHERE myIndex = @myCounter

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

Re: How to check for a never populated table on sql server 2005

Roberto Mazzone3-Aug-09 21:25

Roberto Mazzone

3-Aug-09 21:25

Thanks for replay leckey.

I try to explain better.
I want to predict what will be the next identity value for a table.
If a table has 1 row inserted and deleted, the table is empty and populated.
I this case IDENT_CURRENT return 1 and the next id value will be 2. Correct.
If a table is empty and never populated IDENT_CURRENT return 1 again (it's a bug) and the next id value will be 1. In this particular circumstance i can't predict the next id value and i would like to know a way to check if a table was populated to achieve my goal.

With your code I can't predict the next identity (autonumber) value in the scenario above explained.

Anyway, thanks again.

Re: How to check for a never populated table on sql server 2005

David Skelly3-Aug-09 22:43

David Skelly

3-Aug-09 22:43

IDENT_CURRENT is not a reliable way of predicting the next identity. It can give you a probable value, but you can never be sure of what the next value will be in practice because of concurrent inserts, rollbacks, etc. It will only give you a "best guess" at what the next identity value is likely to be. The bottom line is that you can't predict the next identity, not reliably.

If you need to work around this bug, you could use an insert trigger to update a counter value in another table. That way you can tell how many inserts there have been on your main table. If the counter is 0, your next identity is 1. Otherwise, go and look at IDENT_CURRENT. It's a bit clunky but it's better than doing an insert then a delete.

If you really need 100% predictability of the next identity value, you will probably be better off not using IDENTITY but rolling your own solution (it's not hard - people used to do it all the time before IDENTITY came on the scene). That way you can be completely in control of what value gets allocated to each new insert.

Build your own identity value

David Mujica4-Aug-09 2:58

David Mujica

4-Aug-09 2:58

I agree 100% that the most reliable way to solve this would be to build your own sequence generator where you are in control of the "identity" values. Wrap the "getNextSequence" function call and your insert record logic in a transaction to guarantee things stay in synch.

Before you go and build something, I would re-think the algorithm that needs to know the "next" identity. Is this really necessary ? The whole idea behind an identity column is that it provides a unique number for the row and you shouldn't care what that value is until the row is actually created. Give it some thought. I like to keep things simple. Smile | :)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.