Need help on small project from SQL professional

Question

Post reply

Need help on small project from SQL professional

mostar5

Old Hand

Points: 303
More actions
March 29, 2008 at 7:45 pm

#188559

Hi, I need help on one or possibly more SPs. Please email me at mostar5@yahoo.com.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

mostar5 (3/29/2008)
Hi, I need help on one or possibly more SPs. Please email me at mostar5@yahoo.com.

How much are you paying for the private consultation you're asking for? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mostar5 Old Hand Points: 303 More actions · Answer 2

$$ is negotiable, after I give details you can tell me what will it take.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Why not just post the problem(s) and see what you can get for nothing on this forum? Maybe people will even bid for the "job". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mostar5 Old Hand Points: 303 More actions · Answer 4

ok, here it is; It's all about users creating/saving job searches and based on their setting they get results emailed to them. So i have USER table which contains user login info and also SEARCH_ZIP[nvarchar(5)](this is if user select specific community), SEARCH_COMP[nvarchar(4)](if user select specific company) and KEYS[nvarchar(255)] (keyword that user selects). Also there are 4 other related tables which have userID and numeric value of their selection,

USERCAT fields: UseridID[int], CAT[int] (list of categories user selected)

USERLOC fields: UseridID[int], LOC[int] (list of locations user selected)

USERDUR fields: UseridID[int], LOC[int] (list of job duration user selected)

USEREDU fields: UseridID[int], LOC[int] (list of education requirements user selected)

on the other side there's JOBS table and then related tables for job selections

JOB_CAT fields: JobID[int], CAT[int] (list of categories for Job)

JOB_LOC fields: JobID[int], LOC[int] (list of locations for Job)

JOB_DUR fields: JobID[int], LOC[int] (list of job duration for Job)

JOB_EDU fields: JobID[int], LOC[int] (list of education requirements for Job)

so what I have to do is

IF there's user.keys then look for that keyword in JOBS.PROFILE (LIKE %'keys'%)

IF there's USER.COMP then select only jobs where CLIENT_ACRONYM = 'USER.COMP'

IF there's USERCAT.USRID then match all of USERCAT.CAT with JOB_CAT.CAT

and so on for all of the selections.

The end result should be a new table that only has UserID and JobID

Right now I have this created on using ServerSide but it's too slow. I have about 100,000 users that this needs to be done foron daily basis and ideally in no more then 6 hours.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

If I may suggest...

Read the article linked in Jeff's sig, then post the question again. It's probably simple, but I don't feel like guessing as to table structure and expected output....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Jeff Moden (3/29/2008)
Why not just post the problem(s) and see what you can get for nothing on this forum? Maybe people will even bid for the "job". 😉

Jeff, man you are killing me! You know, some of us have to pay the rent!

🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mostar5 Old Hand Points: 303 More actions · Answer 8

mostar5

Old Hand

Points: 303

March 31, 2008 at 7:52 am

#795948

Anyone?

GSquared SSC Guru Points: 260824 More actions · Answer 9

Something like this should do it:

;with

Cats (JobID) as

(select jobid

from job_cat

where cat in

(select cat

from usercat

where userid = @userid)

or not exists

(select 1

from usercat

where userid = @userid)),

Locs (JobID) as

(select jobid

from job_loc

where loc in

(select loc

from userloc

where userid = @userid)

or not exists

(select 1

from userloc

where userid = @userid)),

Durs (JobID) as

(select jobid

from job_dur

where loc in -- Are you sure this table has "loc"?

(select loc

from userdur

where userid = @userid)

or not exists

(select 1

from userdur

where userid = @userid)),

Edus (JobID) as

(select jobid

from ... -- and so on through your various user-tables

select jobid

from cats

intersect

select jobid

from locs

intersect

select jobid

from durs

intersect

select jobid

from edus

... -- through your various sub-tables for jobs

You'll have to complete it with the various sub-tables, test it for performance, etc. But it should do what you need once completed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

mostar5 (3/31/2008)
Anyone?

Sure.

Read what I wrote above. Help us with the table structures and sample data and someone will help you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

He's all your's, Barry 😉 Send him an email 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 12

mostar5 (3/30/2008)
IF there's user.keys then look for that keyword in JOBS.PROFILE (LIKE %'keys'%)

This is certainly killing your performance. There is no way this is SARGable. Have you considered something like Full Text indexing?

K. Brian Kelley
@kbriankelley