INSERTing new record (performance issue)
-
Trying to transfer from old forum to new forum.
INSERT INTO yaf_Topic(cut,UserID,UserName,cut)
SELECT ...,
(SELECT TOP 1 Author_ID FROM MNPForums...Thread thread WHERE topic.Topic_ID = thread.Topic_ID ORDER BY Message_date) AS UserID,
(SELECT Username FROM MNPForums...Author WHERE Author_ID = (SELECT TOP 1 Author_ID FROM MNPForums...Thread thread
WHERE topic.Topic_ID = thread.Topic_ID ORDER BY Message_date)) AS UserName,
etc
I think the sucker is slowing down because I'm calling the (SELECT TOP 1 Author_ID FROM MNPForums...Thread thread
WHERE topic.Topic_ID = thread.Topic_ID ORDER BY Message_date) two times.
I'd like to think that I could just like 'Author_ID = UserID' but that don't work.
Thanks for any help you can give.
-
Also, the query is trying to transfer about 60,000 records from an Access database to the new SQL Server one.
It's timing out. Is there anyway for me to disable the timeout? I could just run it at night, and I don't really even care if it takes 12 hours (which I don't think it would), I just want it to complete. I'm hoping that it's just timing out because my SQL sucks...but if not?
-
Why cant you call the SELECT TOP 1 1x time write the output into a VARIABLE and use that?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Cuz I dunno how
I've been trying to do that, but I'm SQL Stupid!
-
G'day,
Assuming that your tables look something like the following, the select
may do what you are asking. This is pseudo-code and needs to be adapted
to your specific situation. However, there may be a better way to
approach the problem.
I would suggest dumping the Access tables to a CSV file, and importing them
into SQL Server using BCP or Bulk Insert. Once in SQL server then your
performance problems will be much less. You said that you only have 60,000
or so messages, so the time to export/reload will be minimal.
create table thread (
author_id int,
topic_id int,
Message_date datetime
)
create table author (
author_id int,
UserName varchar(255)
)
create table topic (
topic_id int,
topic_data varchar(255)
)
select top 1 thread.author_id as UserID,
Author.UserName as UserName,
from thread
inner join author on author.author_id = thread.author_id
inner join topic on topic.topic_id = thread.topic_id
order by topic_id, Message_date
Hope this helps
Wayne
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply