Database

Re: Very Tough CSV Data Import to SQL 2005 DB Table Challenge

14-Jul-10 19:33

Does the column count vary per CSV file or are the number of columns different per row.

1
Read the title row, create a table based on the titles

2
Read the entire file create a table with the max columns

This article [^]may help with the techniques

Never underestimate the power of human stupidity
RAH

dsabjp17-Jul-10 16:37

dsabjp

17-Jul-10 16:37

Good ideas. I seem to have found an option that works pretty good. Using OPENROWSET my import works okay as long as the table has equal to or more columns than the csv file. In answer to your question, it is possible that different rows have different number of fields. So far in the examples I've played with Openrowset allows records to be added and any missing columns are taken off from the last columns. I can live with that since I can delete the rows later if needed. Now for the few records that were possibly causing the whole import to crash, I now get the whole import and can do a search and delete rows afterwards. Sorry for the delayed response. I've just got back to my computer. Many thanks for your ideas Mycroft, I would try them next.

Smile | :)

Dean

IN Clause Best Practices? [solved]

Dan Mos14-Jul-10 6:41

Dan Mos

14-Jul-10 6:41

Hy,

I have a problem in that I don't know what to choose.

We have somewhere DataLayer/BussinessLogic something like:

public class MainClass{
.
.
.
protected ObservableCollection<SecondaryClass> lst;//contected to the main class through IDs in the DB
.
.
.
}

So the question is:

What is the best way to get some rows based on a array/list of IDs from the "main" table:
1) Getting all the MainClass rows/objects than generating a string from code(c#) using StringBuilder with all the IDs separated by commas and run a query/storedproc to get all objects from the realted
table(s) with a string/varchar paramter for the IN clause and fill the list of SecondaryClass objects from the MainClass or
2) Generate a temporary table with the IDs and use that in another
query/proc as the IN or EXISTS filter?

I've run some tests and for some reason or another from the speed point of view the first one is
the winner. But both are pretty fast. So speed alone is not that big of an issue.

Because of the Object Model we can not use a join. I mean we could but makes no sence.

Any suggestions will be apreciated.

Thanks

I bug

modified on Wednesday, July 14, 2010 1:47 PM

Re: IN Clause Best Practices?

Eddy Vluggen14-Jul-10 7:10

Re: IN Clause Best Practices?

14-Jul-10 7:10

Hi,

Have you considered Table Valued Parameters[^]?

I are Troll Suspicious | :suss:

Dan Mos14-Jul-10 7:24

Dan Mos

14-Jul-10 7:24

Nope.

Nice solution.
Thanks

I bug

Re: IN Clause Best Practices?

Eddy Vluggen14-Jul-10 7:35

Re: Accessing mdiParent form menu from mdiChild form via code

14-Jul-10 7:35

You're welcome Smile | :)

Message Removed

14-Jul-10 0:00

AussieLew

14-Jul-10 0:00

Message Removed

Richard MacCutchan14-Jul-10 1:28

Richard MacCutchan

14-Jul-10 1:28

I'm not sure what this has to do with "General Database" however ...

In your hormoneTypeToolStripMenuItem_Click you will need to use a reference to your MainForm, either make it global or ensure this method is part of your MainForm. Also note that your HormoneTypeForm f1 will be destroyed as soon as this method returns; I doubt that is what you want.

It's time for a new signature.

Access 2007 - black hole forms

john john mackey13-Jul-10 7:30

john john mackey

13-Jul-10 7:30

I am noticing some freakiness in MS Access 2007.

I had a DB application with 2 forms I use in a utility capacity (to produce a listing of tables within and properties, the other to produce a listing of objects - form names, macro names, query names, ...)

I decided to copy those forms to another application so that DB application #2 can have the same utility features.

I did a Ctrl-A select all on the controls from Form 1, db App#1 into new form, db App#2. Likewise for the other Form 2.

Now in db App#1, i can't display Form 1 or Form 2. I even go directly to the Navigation pane and select Form 1, choose Open.... NOTHING Confused | :confused:

I don't know what happened with Access 2007?!?!?!

Richard MacCutchan13-Jul-10 22:19

Richard MacCutchan

13-Jul-10 22:19

A stupid question but, did you use cut (ctl-X) instead of copy (ctl-c)?

It's time for a new signature.

john john mackey15-Jul-10 12:05

john john mackey

15-Jul-10 12:05

From DB App #1 (source), I selected all controls (Ctrl-A); copied (Ctrl-C); went into DB App #2, on a blank form; pasted (Ctrl-V).

I had a co-worker look it over and he went into DB App #1, did a change to code (then undid it - basically forced a modification to be recognized), saved DB App #1.

DB App #1 then worked as before!

Craziness of MS Office products Unsure | :~

Thanks for the reply

Eddy Vluggen14-Jul-10 4:31

14-Jul-10 4:31

Might be a missing reference. Are you using any ActiveX-controls or other special controls in the first project?

You can check the current references in the Visual Basic editor, first item in the "Tools" menu.

I are Troll Suspicious | :suss:

john john mackey15-Jul-10 12:10

john john mackey

15-Jul-10 12:10

Both Apps (source copied from, DB App #1 and destination copied to, DB App #2) have the same referencing as they use similar Active X controls and reference similar objects (like Excel, etc...)

It is the source, original DB App #1 that is experiencing the problem - only copied the controls from a form in DB App #1 and pasted them into a blank form in DB App #2 (Ctrl-A, select all controls; Ctrl-C, copy from DB App#1; Ctrl-V, paste into DB App #2).

A co-worker took a look and did this: "faked a change" in DB App #1 VBA code (made change/undid change). Saved the VBA code that supports DB App #1.

Then DB App #1 (the source copied from) worked as before!

Craziness that is MS Office Unsure | :~

Thanks,
John John

Eddy Vluggen15-Jul-10 12:34

Interesting SQL Server 2008 problem

15-Jul-10 12:34

Whehe, cool collegue! Cool | :cool:

I are Troll Suspicious | :suss:

SomeGuyThatIsMe12-Jul-10 4:10

SomeGuyThatIsMe

12-Jul-10 4:10

I have to servers that are just about identical in terms of hardware and performance. One dev and one production, both running sql server 2k8. Both of them afaik are configured the same, dev just has an older smaller copy of the production database, its only smaller because of its age. One of its tables has 40M rows, the same table in prodcution has 60M. I needed to make a query faster so i did soem research and since this table (i dotn want to hear any complaints about this next bit) doesnt have and really cant have a PK i was looking at making a non clustered index, or at least changing the one it has. There are so many inserts, updates and deletes i imagine a clustered index would get destroyed in a few hours, or cripple performance.

There is currently an index on Id, its not unique just a way of grouping records that go together. I read about the INCLUDE keyword that was new in server 2k8 and how it should speed up selects if i included the columns in the where and select clause in the index so it wouldnt have to do the RID or Bookmark lookup(its where 99% of my time was bieng taken).

So i changed the index in dev, added every column in the table(since we always use every column in the table any time we go to it, and there's only 10 or so).

so the index was CREATE INDEX uix_name id INCLUDE (col1, col2, col3, ...). The SELECT's performance skyrocketed, but since i now have an index that looks at every column, updates and deletes should run slower..much slower. They didnt, they ran even faster. This is the part i dont understand at all.

I put the same index on the production server, and the select ran faster, but updates, deletes, and inserts all got much much slower(this i would have expected).

for a comparison i uploaded 1M records into the table, on dev it took 2:48 after index, it wouldnt finish before(through our wep app not through the db), in production it took 4:21 ish(no index changes) and over 6 minutes after i changed the index.

Can anyone please offer any insight as to why my dev server got faster and the prodcution server did what we thought it should and got slower? I have no idea where to even start looking.

Thanks

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Mycroft Holmes12-Jul-10 14:45

12-Jul-10 14:45

Dev servers, due to their close relationship with, well developers, tend to be better behaved than production servers and they quickly learn the consequences of disruptive behavior (the 3 fingered salute is not unknown to a dev server). Surely you have been in the position where a user cannot get something working until you go stand beside them, then the dammed thing behaves itself. The same applies to servers.

Also SQL likes to throw in the odd bizarre behavior occasionally, one of the favourites is to take 10-100 times as long processing a query from the application than it does in SSMS.

To date I have not found a solution to this odd behaviour, however I have found it beneficial to NOT discuss these aberrations with my manager! Good Luck...

Never underestimate the power of human stupidity
RAH

SomeGuyThatIsMe13-Jul-10 2:59

SomeGuyThatIsMe

13-Jul-10 2:59

I did, finally, notice 2 differences in the configuration of the databases. The compatibility mode and Page_Verify settings were different. Dev was set to 80 (sql server 2k) and torn page, prod was set to 100(server 2k8) and checksum. I was under the impression that new fancy features wouldnt work on a database unless the compat mode was set to the correct version. I checked the queries(update and select) in SSMS and compared execution plans and they looked identical except for the time taken.

I forgot to mention that we use Bulk Insert to insert the million rows, dont know if that would make a difference or not.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Corporal Agarn13-Jul-10 5:12

Corporal Agarn

13-Jul-10 5:12

SQL 2008 does some strange things on bulk insert. I have had to rewrite several processes that worked just fine on 2K that do not work on 2008. Import wizard in SSMS does not import things that Enterprise Manager did fine.

bcp and int>ASCII code conversion [modified]

SomeGuyThatIsMe13-Jul-10 5:17

SomeGuyThatIsMe

13-Jul-10 5:17

I have noticed a few cases where the import wizard had turned into a complete pile, i really miss Enterprise Manager, Object Explorer annoyes the crap outa me. I cant figure out why an index change that shouldnt be supported on the database made everything faster, and screwed up everything but reading on the database where it should have been supported.(actually the last bit is what i expected to happen, so i understand that just fine).

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

devvvy11-Jul-10 20:00

devvvy

11-Jul-10 20:00

hello

I'm trying to bcp in matrix of integers into SQL using SQL Server bcp.exe

<br />
1,2,3<br />
3,4,5<br />
6,7,8<br />

And bcp command as follows:

<br />
bcp.exe DEV.dbo.SomeIntData in C:\BcpTest\bcp\output\output_int_pc.txt -S SomeServerName -f int.fmt -T  <br />

In my formatting file:

<br />
9.0<br />
3<br />
1       SQLBIGINT    0       25       ","    1     Col1         ""<br><br />
2       SQLBIGINT    0       25       ","    2     Col2         ""<br><br />
3       SQLBIGINT    0       25       "\n"    3     Col3        ""<br><br />

My simple test table:

<br />
CREATE TABLE [SomeIntData](<br />
 [Col1] [int] NULL,<br />
 [Col2] [int] NULL,<br />
 [Col3] [int] NULL<br />
) <br />

Problem is, integer value of "1" when inserted (by bcp.exe) into database became "49" (ASCII value/code of "1") - Any suggestion?! Thanks!

http://www.asciitable.com/[^]

http://msdn.microsoft.com/en-us/library/ms191479.aspx[^]

http://msdn.microsoft.com/en-us/library/ms189110.aspx[^]

dev

modified on Monday, July 12, 2010 2:37 AM

Re: bcp and int>ASCII code conversion

Mycroft Holmes11-Jul-10 23:18

Re: bcp and int>ASCII code conversion

11-Jul-10 23:18

Just an observation

We found that the BCP.exe in SQL 2008 was a lot more unstable than earlier versions. Apparently MS tightened up some of the formatting rules and it basically broke BCP. If you can make it behave then it is still the fastest load method.

We do not do any tansformation during the load, shove everything into varchar fields in a staging table. We then use stored procs to do the transforms. This gives us a number of advantages:

No errors in the load
Easier to manage the errors in the data when it is right there in front of you.
Dramatically faster - I mean hugely faster than either SSIS or Biztalk.

Never underestimate the power of human stupidity
RAH

devvvy12-Jul-10 0:29

devvvy

12-Jul-10 0:29

nooeee.... I have no time to play around or experiment - and how do you implement "Stored procs to do transforms"?

Thanks

dev

Re: bcp and int>ASCII code conversion

Mycroft Holmes12-Jul-10 14:32