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 2000 Forums
 SQL Server Development (2000)
 70000 queries

Author  Topic 

AlexCold
Starting Member

39 Posts
Posted - 2002-06-17 : 05:18:49
Hello, guys!
I need to run up to 70000 queries at once. Can somebody advise me how to do it and to do Not bring al things down? Or 70000 at once can't bring sql 7 down?


--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2002-06-17 : 06:12:56
Got any more info? The question is a little sparse on detail for anyone to be able to give you a definate answer. Are they the same queries run by 70000 users, are they different queries, what will they do etc....

Peace

Rick
Go to Top of Page

kristian
Starting Member

21 Posts
Posted - 2002-06-17 : 06:43:05
I had to do a similar thing on my site only i had 125000 to do.
I presume a command for each row in a db somewhere. What i ended up doing was creating a new bit field as a marker so i knew if the corresponding row had had its command executed. Then i wrote a page that executed 10 queries, updated the bit field as it went so it knew which ones had been done. The page then had a auto refreshed every 5 seconds (To give the server a bit of breathing space.) And executed the next ten commands. I just then openened the page and let it tick over for a few days.

I actually made the number 10 a page variable that could be changed by adding ?qty=20 to the page url, so i could up the number a get more done and then when i saw the server suffering because of it id lower it right down again.

Anyway this way worked for me.

Kristian
Go to Top of Page

AlexCold
Starting Member

39 Posts
Posted - 2002-06-17 : 07:19:46
quote:

Got any more info?

Two tables...

CREATE TABLE tbl_Voter (
ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
FirstName varchar (25) NOT NULL ,
LastName varchar (30) NOT NULL ,
Address varchar (50) NULL ,
Zip int NULL ,
CityID int NULL ,
InitiativeID int NOT NULL ,
PetitionID int NOT NULL ,
Status tinyint NOT NULL
)

CREATE TABLE tbl_Citizen (
ID int identity(1,1) NOT NULL PRIMARY KEY,
FirstName nvarchar (50) NULL ,
LastName nvarchar (50) NULL ,
Address nvarchar (70) NULL ,
CityID int NULL
)


First table contains from 1'000 to 70'000 records.
Second table contains up to 3'000'000 records.
Data in the first table is entered by user(s).
Data in the second table is replacing with new one once at month.
When new data is on the server I need to run query which will find match between each record from the first table and a record in the second table. If match is found "Status" field for this record must be set to 1, else no updates needed.


--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/
Go to Top of Page

AlexCold
Starting Member

39 Posts
Posted - 2002-06-17 : 07:24:10
quote:

I had to do a similar thing on my site only i had 125000 to do.

Your solution is near the best! Only one thing... I need a way to do it inside sql server. I don't want to mess with application servers...


--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2002-06-17 : 08:33:28
Hi Alex,

There was this discussion on naming conventions on SQLTeam. follow this http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=16751 .

Am little curious what does your 70,000 queries do? .

if they achive only this , then there are easy ways of doing this. can you clarify ?
quote:

First table contains from 1'000 to 70'000 records.
Second table contains up to 3'000'000 records.
Data in the first table is entered by user(s).
Data in the second table is replacing with new one once at month.
When new data is on the server I need to run query which will find match between each record from the first table and a record in the second table. If match is found "Status" field for this record must be set to 1, else no updates needed.


-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson
Go to Top of Page

AlexCold
Starting Member

39 Posts
Posted - 2002-06-17 : 09:48:23
Nazim,

I'm not trouble-maker. But...

Why do you point me to the "naming conventions on SQLTeam" topic? I name my fields in my database as I want. How my question intersects with the "naming conventions on SQLTeam" topic?

quote:
Am little curious what does your 70,000 queries do?

Please, read once again my previous posting.

8<----------------------------------
Query will find match between each record from the first table and a record in the second table. If match is found "Status" field for this record must be set to 1, else no updates needed.
8<----------------------------------

I know at least 5 "easy ways of doing this". Saddenly I need one! Only one! Which won't bring server down, because no one will unplug all clients from the server to give me ability to run this query (or bunch of queries). I need a way to run it on the live server with it normal workload.
If you know how to create a storproc which will work like (or very similar to) kristian's external application (I mean - allocated load) - I'm all ears!!!


--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-06-17 : 10:01:29
Hey Alex, relational models work on sets. Iterative operations are far from optimal in the relational world; however, set based methods work quite well.

How about something as simple as...

update
    tbl_voter
set
    status = 1
from
    tbl_voter v
    inner join tbl_Citizen c
        on v.id = c.id
go

 
quote:
I presume a command for each row in a db somewhere.

If you approach database programming with row-by-row operations (like a 4gl programmer), especially when you are dealing with large sets of data, you will have nothing but heartache. However, if you operate on sets of data, you'll find that databases work quite well.

<O>
Go to Top of Page

AlexCold
Starting Member

39 Posts
Posted - 2002-06-17 : 10:31:29
quote:

Hey Alex, relational models work on sets.


Hi, Page47!
tbl_voter.id and tbl_Citizen.id aren't corresponding fields. So joining those tables in this way won't be correct.

I love your solution!
Can you help me a bit? How do I need to alter your query in order to "join" (sorry, here can be miscommunication, since english isn't my native language) two tables on "more then one field" (I understand that it is oxymoron) and to check if comparison returing only one match (zero or more then one won't change "Status" of the record).


--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2002-06-17 : 10:39:49
i was referring to your table names Alex. didnt mean any offence though.


update
tbl_voter
set
status = 1
from
tbl_voter v
inner join tbl_Citizen c
on v.commonkeys = c.commonkeys and v.anotherkey=c.anotherkey
go




-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-06-17 : 10:58:28
quote:
tbl_voter.id and tbl_Citizen.id aren't corresponding fields. So joining those tables in this way won't be correct.

You're gonna need to help me help you. Post some sample data from your two table and the expected result. You've thrown a wrench into it by looking for 'exactly' one match . . .

Here's another shot in the dark using a correlated subquery to capture only the 'exactly' one matches...

update tbl_voter
set
    status = 1
from
    tbl_voter v
where exists (
        select
            v2.[id]
        from
            tbl_voter v2
            inner join tbl_citizen c2
                on (v2.FirstName = c2.FirstName and
                    v2.Lastname  = c2.LastName and
                    v2.Address   = c2.Address and
                    v2.CityID    = c2.CityID)
        where
            v2.[id] = v.[id]
        group by
            v2.[id]
        having
            count(*) = 1 )

 


<O>
Go to Top of Page

AlexCold
Starting Member

39 Posts
Posted - 2002-06-17 : 11:42:47
quote:
You're gonna need to help me help you. Post some sample data from your two table and the expected result.


Can you drop me a line [url]reader@faqteam.org[/url]?


--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-06-17 : 11:50:42
quote:
Can you drop me a line [url]reader@faqteam.org[/url]?


No, I'd rather go through this process in public as it may be helpful to others.

<O>
Go to Top of Page

AlexCold
Starting Member

39 Posts
Posted - 2002-06-17 : 15:56:36
Page47,

Your code with several specific addtitions is covering my problem with 100% precision. Solution is really neat!

Thank you, MAN!
I'm your debtor with a beer!!


--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site