Bulk Insert from a view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bulk Insert from a view

Author

Topic

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 10:58:59

I don't believe bulk insert can be run from a view so I need to come up with something similar. I have a view that manages 15-20 relationships and when it runs it is very time consuming for repoting purposes. I need to save the data from the view into a temporary table and run the reports from that. The problem I am currently having is it takes 9 minutes to run the following job step.

INSERT INTO MakeTable
(... Fields ...)
SELECT
... Fields ...
FROM ViewName

The application has only been in use for 8 days. At this rate it will take an hour to run this script after the tool has been in production for 3 months.

BTW - I am not the SA. I have dbo rights and bulk insert rights on the SQL 2000 box.

Thanks for any help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:08:17

have you tried a stored procedure that would export the data to txt file
with bcp and then import them to a temp table?
maybe that would be faster.

if you do just the select how long does it take?

what kind of dataload are we talking about?
do you have proper hardware, especialy disks?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 11:18:52

Thank you for your help.

I have not tried the txt file approach. I do not have write permissions to any of the local drives on the server. Could I export the data to a web server and then read it back into the SQL Server?

If I run a select * directly on the view it takes about 20 seconds to provide any results. If I hit ctrl + down to see all of the records it takes close to 9 minutes to return all of them.

The dataload is ~15000 records.
It's currently hosted in a datacenter of a fortune 350 company. I don't have direct access to the server.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:22:45

20 seconds for 15k records???

you have indexing issues.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:24:17

one way to go is to index your view... that would make selecting way faster

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 11:24:23

I would love to have it return the 15k records in 20 seconds. That was only for the first screen. It takes 9 minutes to return all 15k records.

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 11:25:42

When I right click on a table I have index options, but when I right click on the view "manage indexes" is disabled. How would I do it?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:28:36

open books online (sql server help)
and type in "indexed views"
there are some conditions for this though.
you'll have to recreate your viwe wiht schemaBinding option which means that you won't be able to change schema of any
table in the view while this view exists.
all your columns must be deterministic or persisted.

read about it and you'll get the picture.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 11:31:31

Thank you very much.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:37:43

you're welcome.
oh and do note that i still think you have serious issues with proper indexing of your database.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 11:38:49

I'm going to spend the next few hours researching all of the indexes and attempting to maximize the performance of this process.

Thanks again.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:40:03

excellent

a tip:
indexing of columns that are join or filtered on is good

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 11:50:33

I am having some problems changing the indexes. They seem to be setup with primary keys but sometimes that is not what I'm filtering or joining on. When I try to add an index I get the following error:

Cannot re-create index 'PK_....'. The new index definition does not match the constraint being enforced by the existing index.

Thanks again for all of your help.

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 12:10:10

If I remove the PK from a table it will still auto-increment right?

Example:
CustomerID CustomerName City CustCode
1 ABC Company Detroit ABC
2 123 Company Orlando 123
3 John's Towing Las Vegas JNT

If I remove the current PK on CustomerID and setup an index on CustCode would it cause any problems? Would I have to change any of the source code that is adding records to this table. The source code is currently adding all fields except CustomerID because it is set as an identity.

Thanks again.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 12:16:49

identity doesn't have to be a PK. it'll stil work if you remove the PK.

also note that good candidates for clustered indexes are unique always incrementing values.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 12:22:05

If I have a sales table that is always joined by SalesRepID and filtered by SaleDate should I not have a primary key? Which should have the clustered index? The SalesRepID or the SaleDate?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 12:27:36

which is a better candidate based uniqueness, ever increasing values,
sorting preference (clustered index sorts the data in the columns it contains),...

try different variations and you'll see which is best.
your goal is to reduce physical and logical reads.
you can see these by running
SET STATISTICS IO ON

time taken for each statement can be seen by running
SET STATISTICS TIME ON

also look at the execution plans
index seek is good, index scan is bad.

also this might come in handy:
http://weblogs.sqlteam.com/mladenp/articles/9502.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-11 : 13:56:57

I indexed the tables properly and the entire job that truncates the report table and inserts the new records (15k) takes about 15 seconds instead of the 9 minutes it was taking early today.

Thank you very much for all of your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources