Database

23-Nov-10 7:43

You're welcome.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Timeourt

logicon12-Nov-10 1:00

logicon

12-Nov-10 1:00

Hi All,

I have a table with 50000000+ rows in SQL Server 2008 R2 Express. I am querying this database table from a .NET application. Unfortunately this table is not having any PK and probably its now possible to implement one.

When I fire my query it doesn't works due to Timeout. I get error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I am reading this with execute dataset method to fill my Datagridview.

Please suggest me approach I should take to make this work.

thanks

Goutam Patra12-Nov-10 1:44

Goutam Patra

12-Nov-10 1:44

Increase CommandTimeout period to avoid this error

logicon wrote:
50000000+ rows

However I would suggest whenever you are pooling such a huge data in one dataset, if possible, try to fetch data in small chunks.

Luc Pattyn12-Nov-10 2:24

12-Nov-10 2:24

millions of rows in a DataGridView? I pity the users then.
This forum holds some 50,000 messages, yet you see only a few (say 25) of them at once.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Use a where clause

David Mujica12-Nov-10 3:08

David Mujica

12-Nov-10 3:08

Sounds to me like you are trying to fetch way too many rows in one shot. You should re-think your logic and select only the rows you really need in your applicaiton.

If you really need to accessing millions of rows, then maybe you should be using a datareader and not try to fill a dataset.

Why don't you post a brief summary of what you are trying to do any the folks here will give you all kinds of ideas on how to attack the problem.

Good luck. Thumbs Up | :thumbsup:

Luc Pattyn12-Nov-10 3:10

12-Nov-10 3:10

you're talking to the wrong person here.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Oops, my bad

David Mujica12-Nov-10 5:32

David Mujica

12-Nov-10 5:32

Yep.
I replied to the wrong thread. My previous comment should have been applied to the original author, "logicon".

Sorry Luc.

logicon12-Nov-10 9:03

logicon

12-Nov-10 9:03

Hi David,

You are correct, I should use datareader.

The requirement is to provide a tool which brings rows in Excel from SQL Server so that end user can play with it using pivot and other excel stuff. What I initially planned was to write Excel Add-in. Unfortunately due to version issue (I had Excel 2007 and end user is having Excel 2010) I decided to write Windows Form Application which will have a DataGridView and "Export To Excel" button.

Internally, SqlDataAdapter 's Fill is used here in my current code.

I will try the DataReader and share the result with all you

Thanks Everyone.

PIEBALDconsult12-Nov-10 9:36

PIEBALDconsult

12-Nov-10 9:36

Use a DataReader to write a CSV file -- the user can then open the CSV in Excel. Slick as snot.

Mycroft Holmes12-Nov-10 10:58

Mycroft Holmes

12-Nov-10 10:58

Use SQL server to output the resultset to a CSV file on a shared folder - slicker than snot!

Never underestimate the power of human stupidity
RAH

PIEBALDconsult12-Nov-10 15:23

PIEBALDconsult

12-Nov-10 15:23

Not as portable as snot.

thatraja12-Nov-10 2:47

thatraja

12-Nov-10 2:47

logicon wrote:
50000000+ rows

It's horrible to load at a time, use paging technique in query. An example SQL Server 2005 Paging Results[^]

thatraja |Chennai|India|

Brainbench certifications
Down-votes are like kid's kisses don't reject it Smile | :)

Do what you want quickly because the Doomsday on 2012 Smile | :)

Create table from xml file

jschell16-Nov-10 9:03

jschell

16-Nov-10 9:03

logicon wrote:
I have a table with 50000000+ rows in SQL Server 2008 R2 Express...Datagridview

You need to change your requirements.

And what is the growth rate of this table? What happens when it is 10 times bigger than it is now?

Hum Dum11-Nov-10 22:07

Hum Dum

11-Nov-10 22:07

I have a xml file on which i have to operate.

I am using following approach

Declare @c_cmd VARCHAR(255),@C_940IN_FILE varchar(100),@C_940IN_DIR varchar(100)<br />
<br />
Set @C_940IN_FILE ='S_outbound.xml'<br />
Set @C_940IN_DIR ='\\xceed\tech\'<br />
<br />
 CREATE TABLE #TEMP_940    <br />
 (    <br />
  ROWDATA varchar(8000) Null    <br />
 )    <br />
   <br />
 SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + ''''    <br />
   + ' WITH (FIELDTERMINATOR = ''><'')'    <br />
 PRINT @c_cmd    <br />
 EXEC(@c_cmd)    <br />
-----------------------    <br />
 select * from #TEMP_940  <br />
<br />
Drop Table #TEMP_940

.
.

It gives me temp table with all nodes, like

   <Case_Dimension><br />
<br />
    <unit_of_measure>PK</unit_of_measure><br />
<br />
    <quantity>6</quantity><br />
<br />
    <unit_length/><br />
<br />
    <unit_width/><br />
<br />
    <unit_height/><br />
<br />
    <dimension_measure>CM</dimension_measure><br />
<br />
   </Case_Dimension>

Now i have to use a cursor for getting all the values of corresponding fields.

Is there any better way?
By which i get table which will create a temp table like, without using cursor

<br />
unit_of_measure     quantity ...  ....  .... so on<br />
---------------     --------<br />
______PK_______     ___6____ ...  ...  ....<br />

regards

Re: Create table from xml file

Eddy Vluggen13-Nov-10 9:18

Eddy Vluggen

13-Nov-10 9:18

I have read the question a couple of times, but I'm still having trouble understanding your query. So, let's verify what we got;

Hum Dum wrote:
I have a xml file on which i have to operate.

That's a physical file, located on the harddisk? I mean, it's not stored inside an SQL Server table or anything like that?

Hum Dum wrote:
Now i have to use a cursor for getting all the values of corresponding fields.

To make sure I understood that correctly; you're first reading the columns from that file, and you'd be fetching it's values with a cursor?

Is it a requirement to use Sql to import the data, or would it be allowed to use C# or VB.NET? How will the import-process be started, does it get run by the server automatically, or does the user init the import?

Hum Dum wrote:
Is there any better way?

There might be, depending on your requirements and restrictions. If you're allowed to program a solution, I'd rather suggest the XmlDocument[^]-class.

If it has to be done from Sql, I'd suggest converting the file from XML to (several?) CSV-files. Then again, if it's Sql Server, then you might even get away with creating a linked server to your file and SELECT INTO the destination table.

I are Troll Suspicious | :suss:

Re: Create table from xml file

Hum Dum14-Nov-10 19:05

Hum Dum

14-Nov-10 19:05

Eddy Vluggen wrote:
How will the import-process be started, does it get run by the server automatically

SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + ''''  
   + ' WITH (KEEPIDENTITY, FIELDTERMINATOR = ''><'', ROWTERMINATOR= ''<Product_Information>'')'

the above command will import xml data in to temp table #Temp_940 (see my original post).
I just need to execute this command and i have the data of XML.

Now i have to operate on this data row by row and insert it into another table. for that i have to use cursor.

Eddy Vluggen wrote:
I'd rather suggest the XmlDocument[^]-class.

I know and used it also. when i suggest to use C# my PM says "NO". Confused | :confused:

"You must use SQL server."

So, its not my choice Cry | :((

Re: Create table from xml file

Mycroft Holmes14-Nov-10 20:37

Mycroft Holmes

14-Nov-10 20:37

Hum Dum wrote:
for that i have to use cursor.

I presume you select statement will not do the job for you

Insert Tablename (columnames....)
select columnnames...
from #Temp_940

Never underestimate the power of human stupidity
RAH

in which version of sql i can get this performance tools & database tuning advisor

vinu.111111-Nov-10 19:00

vinu.1111

11-Nov-10 19:00

hi....

in which version of sql server i can get the below two option...

1) performance tools

2)database tuning advisor..

please send me the download site...

Re: in which version of sql i can get this performance tools & database tuning advisor

thatraja11-Nov-10 19:27

thatraja

11-Nov-10 19:27

SQL Server 2005. But those features(which you have mentioned above) not exists in Express edition so you need buy SQL server 2005 enterprise edition. For more info click here[^]

thatraja |Chennai|India|

Brainbench certifications
Down-votes are like kid's kisses don't reject it Smile | :)

Do what you want quickly because the Doomsday on 2012 Smile | :)

Re: in which version of sql i can get this performance tools & database tuning advisor

vinu.111111-Nov-10 19:31

vinu.1111

11-Nov-10 19:31

thanks .. i got in 2008 enterprise edition....

MySQL-incorrect information in file frm

PeriyasamyRamachandran9-Nov-10 23:24

PeriyasamyRamachandran

9-Nov-10 23:24

Hi,

In my server the MySQL database is not started. Because, the database was corrupted since the table size is large. So i resolved this issue and started the MySQL server by extending the innodb table space.

The below line is i added in my.cnf

innodb_data_file_path = ibdata1:100M:autoextend:max:500M;ibdata2:100M:autoextend

The MySQL is started and database and tables are there. The problem is when i select any table data, i got the error as "incorrect information in file frm".

How can we fix this issue?. Any idea pls.

Regards,
Periyasamy.R

Re: MySQL-incorrect information in file frm

Luc Pattyn10-Nov-10 7:21