Help with SLOW SQL Server performance

I'm still new to SQL Server so some of my lingo/verbage may be incorrect, please bare with me.

The company I work for relies strictly on ASP and SQL Server for 85% of it's daily operations. We have some Access projects and some VB projects as well, but for the majority it's ASP and SQL Server.

Previously we had 2 T1 lines with something like 3MB a piece and a handfull of Dell Servers. Our main server is also a Dell running Windows Server 2003 and is hosted through a reputable company here in town. They have a host of fiber lines running all over so I know we're getting good throughput. We've actually just upgradded to a DS3 but we're still working out the kinks with that. Anyway, I just want to eliminate that up front - we have great connection speeds.

The problems lies, I believe in our database design. The company supposedly had a DBA come in and help setup the design some 3 or 4 years ago, however even with my limited knowledge I feel like something is just not working right.

Our main table is "Invoices" which is obviously all of our Invoices, ever. This table has an Identity field "JobID" which is also the Clustered Index. We have other Indexes as well, but it appears they're just scattered about. The table probably 30-40 fields per row and ONLY 740,000 rows. Tiny in comparison to what I'm told SQL Server can handle.

However, our performance is embarassing. We've just landed a new client who's going to be brining us big business and they're already complaining about the speed of their website. I am just trying to figure out ways to speed things up. SQL is on a dedicated machine I believe with dual Xeon processors and a couple gigs of ram. So that should be ok. THe invoices table I spoke of is constantly accessed by all kinds of operations as it's heart of what we do. We also have other tables such which are joined on this table to make up the reporting we do for clients.

So I guess my question is this. Should the Clustered Index be the identify field and is that causing us problems? We use this field alot for access a single Invoice at a time and from what I understand this makes it a good Clustered Index, because the index IS the jobID we're looking for. But when it comes time to do reporting for a client, we're not looking at this field. We just pull the records for that Clients Number. And we only have 1400 clients at this point. So if we were to make the "ClientID" field the Clustered Index, it would much faster to Zero in on the group of Invoices we wanted because the ClientID is ALWAYS included in our queries.

But because a "DBA" came in to design this setup, everyone is afraid to change it. I guess it's hard to explain without people sitting here going through the code and look at the structures of all our tables - but I guess what I need is like a guide of what to do to easily increase performance on SQL Server and the proper use of Clustered and Non-Clustered Indexs and how to mix and match those.

Sorry I wrote a book.
Ideas? This place has always helped me before, so thanks in advance!The first lesson (ok, first after the "it is not your data" lesson), is to fix problems, and not theories. In order to pin down the exact problem, you have to amass approximately one mountain (mt) of data. Fire up perfmon on both the web server and the DB server. On both look at the following counters:

Memory: pages/sec should be < 100
Processor: %utilization should be < 75%
System: Processor Queue Length should be < 2 * number processors you have
System: Context Switches/sec should be < 10,000

If any of these are significantly out of whack, then you can try to drill down into any one of those.

On the database server side, Profiler and Query Analyzer are going to be your best friends. Trace
Stored Procedures: RPC Completed and
TSQL: SQL BatchCompleted
Look for anything with a duration over 1000. If you see a particular procedure/query commonly running over 1000, then start working over that query. A query that is run once or twice a day is not much to worry over.

In Query Analyzer, use sp_who2 to monitor for blockinig occasionally, and especially during slow times. It could be you just have some interface process that is locking up tables at times throughout the day.

Short of writing a book, this should get you started.|||MCrowley - Thanks for posting back and offering me a starting point on actualy fixing visual problems instead of guessing on what may be the cause. I'll look into the suggested tools and post back on my findings.

Much appreciated.|||Who's the Admin of the Box?

The FIRST thing I would do is to make sure all of your backups and transaction log dumps are ok and successful, by restoring the dumps to a test box.

Do you have restore procedures in place to recover in case of a problem? No? Thatw ould be my second task.

Do the developers use sa to build their code?

I'd revoke all sa access to the database as my third step and make sure only 1-3 individuals hav sa authority...hopefully non developers.

OK, so 4th I'd check for blocking (so_who2 active) and I'd also look at the locks (sp_locks). If you see a lot of table locks, you have a problem...most likely poorly design code.

I would the run a trace and make sure the trace writes to a sql server table.

Here's a template I made up. Make sure you change the host in the filter to a machine id that you are going to test on. Just make sure you change the extension back to .tdf.

I would however seriusly recommend that you hire a qualified dba.

what part of the world are you from?

I got some spare evenings...got some virtual PC going over there?|||The box is administrated by the hosting company primarily. We have limited access. It is our server, but it's on their network in their racks so they handle most of those adminstrative tasks such as updates/hardware/etc. Myself and the rest of the IT dept (6 of us now) all Domain Administration access in Active Directory and access to modify the DB as we see fit. But we don't have an in-house DBA and the guy who HAD the most knowledge got canned for just reasons. So...

We have Vertias in place for Back-Ups that are run every night. I don't know the ins and outs of whether they're full back ups or sequential or what have you, but I do know they work and we recently had one of our staff accidentally omit a where clause on an update which required us to restore a recent backup - so yes, that is tested and working just fine.

Developes us sa to build code? I'm assuming you mean the sa account in SQL Server, but again I could be wrong. I'm learning as I go here. There was "master" account created if you will that is used to access the server throughout all of our production and test code. This is not the "sa" account, but one that was created. When we develop code, we have our own logins and access rights and typically just right the code right in ASP or use Query Anaylzer to test first. When the code is put into production, it uses a general connection string using the master account I spoke of. I hope that answers this.

We do get blocking from time to time, but it's not every day. Still, it happens more often that I'd like. We also get frequent dissconnects. The company we were originally hosted with (we're 90% in house now except for this Server box) claims it's our Firewall and Router. We have no problems with lost connections from anywhere else so we fell this is an issue on their end. But getting back to the point. We do occassionally have blocks. I haven't really investigated them myself, but I can do that.

I've been running a few traces since the first suggestions posted and the trace gets to 15,000 rows in less than a minute. Using the Duration template, I've got to scroll up several pages from the bottom to stop seeing durations over 1000 so there's obviously some performance gains to be made there. Our worst one is a SP that routinely goes over 11,000 for the duration and is ran frequently.

Finally, I'm in the US - OHIO to be exact and we obviously do need a DBA. We've discussed doing that but would like to fix what we can on our own. So we can learn this stuff, and cut down on the cost and time the DBA will actually need to be here.

We do have and VPN and etc setup, but obviously I can't being giving out that kind of info over a message board nor do I have the authority to give it out at all. The company is pretty private with their data. But I DO really appreciate the suggestions and I'll have a look and the attached template.

Much thanks!|||Well I would open up that stored procedure and run a test on it with a SHOW EXECUTION PLAN ([CTRL]+K) and look at the results. Look for scans of any kind.

It could also be that you have a bad plan in PROCACHE. If it is a bad plan you'll need to recompile it.|||OHIO! Blind dude, get over there and help him out...|||Okay now this is where you've exceeded my brain capacity. I executed the SP in Query Analyzer and did the (CTRL + K) thing for showing the execution plan. I also just selected that from the action drop down, but I'm not getting anything so please teach me, wise one! :)

Also how can I check my PROCACHE. Sigh, I can just a see a link to Amazon.com for "SQL Server for dummies" coming my way.

Thanks again for all your help thus far.|||If you did both (selected it, then did CTRL+K), then I would most likely say that you turned it off.

Just go to the menu and select then run the sproc.

I did think you can see the cache, but you can clear it out. DBCC FREEOROCACHE...but that's not a good thing since it will cause everything to have to be recompiled.....wich will slow everything down the first time it's called.

Here's a test.

Make sure the SHOWPLAN is on

Execute the procedure in Query Analyzer...not the length of time and look at the plan

Then do sp_recompile <sprocname>

Execute the proc again. It will take longer because of the compil

Execute the sproc again, then note the time.

IF it is much fatser, then it's a bad plan.

If it's not then it still could be a bad plan and it has no choice.

You might want to show us the sproc.

But tell us what the plan says first

I'm kicking out, but I'll check in in the AM|||The Execution Plan drew out a huge grid diagram with icons and such. Is there anything specific you're looking for?

SPRoc - 1st Run. 40 seconds
SPRoc - 2nd Run. 28 seconds

--Force Recompile
SProc - 1st Run. 1 Mintue 28 seconds
Sproc - 2nd Run. 2nd Run. 30 seconds??
Sproc - 3rd, 4th, 5th, subsequent runs 7 - 15 Seconds! :)

Recompliling the SP seems to have helped, but with your logic that means I have a bad plan?? But I don't even know what a plan is. I'll do some studying and research tonight in prep for more respones.

Cheers!|||You'll be looking for any scans in the output. Also check for wide arrows (indicates many rows returned). Any large table that is getting scanned is probably a bad thing, unless this is a data warehouse/reporting kind of application. Cursors in the stored procedure are also a killer, and usually end up being the first target of any code re-writes.

If you can post the code of the stored procedure in question, we can take pot-shots at suggesting indexes, but those will depend pretty heavily on your data distribution. Have you tried the Index Tuning Wizard? It should be available in Profiler, and definitely should be in Books Online (which is SQL Server for SQL Server users). If you don't have Books Online (aka BOL) then find it on Microsoft's website, download, and install. We may sound like we know it all, but we cheat. We have BOL open from the minute we get in, to about 2 minutes before we leave.

Wait. Did I say that with my inside voice, or my outside voice?|||<div style=Columbo>
Oh, one other thing...
</div>

If you can see blocking, odds are it is a problem. Get to know dbcc inputbuffer (SPID), and try to chase down the blockers. The blockers could be half (or more) your problem.|||CREATE PROCEDURE [dbo].[Envox_AdjustCallLog]

AS

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

UPDATE
Envox_CallLog
SET StartTimeAdj=CallStartTime,
EndTimeAdj=CallEndTime,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='EST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-1,CallStartTime),
EndTimeAdj=DATEADD(hour,-1,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='CST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-2,CallStartTime),
EndTimeAdj=DATEADD(hour,-2,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='MST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-3,CallStartTime),
EndTimeAdj=DATEADD(hour,-3,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='PST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-5,CallStartTime),
EndTimeAdj=DATEADD(hour,-5,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='PST-2' AND Adjusted=0 AND Completed=1
GO

We have an Envox telephony Server in house. This SP is apparently adjusting all the logged call times based on their time zones. This is just one of many problem queries that are taking a while to run. I imagine the 3 joins per update on top of the ZipCodes table which is probably huge is the main issues.

I'm also not the phone guy, so I'd have to suggest any recommned changes to him as I'd be eaten alive if I messed with it on my own. The index tuning Wizard is running as we speak, so hopefully it comes back with some good suggestions.|||I can tell you right now, you're gonna see a scan on Properties

ON LEFT(Properties.Zip,5) = ZipCodes.Zip

Do this for us. Run the code below and post the Results

EXEC sp_spaceused Properties
GO
EXEC sp_spaceused ZipCodes
GO
EXEC sp_spaceused ZipCodes
GO

I can also tell you that I know what he's doing, he's turning everything into GMT Times. Now I've never been a big fan of derived columns, but man this is isure where this would be a good thing. Hell I might even say a TRIGGER might have been better than here. Also a derived column for the zip code so they don't need to use LEFT on the zip column in properties. That's called a stage 2 or nonsargable predicate and will always cause a scan.

Also, do you know how to script a table and get the DDL in Enterprise Mangler? Look at the sticky at the top of the forum here and read the instructions if you don't.

I betcha we can fix it up....|||Oh, and I like the error handling he has in there as well...is this run from and interface or a scheduled batch job...and I wonder about the very arbitrary

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

It's gotta be batch...

He really needed to have a batch window table so he doesn't miss anything...here's the problem with the way he's doing it...if the proc doesn't run for a while...there's no determining how long this damn thing will run...he's just leaving the earliest inserts alone...

Matt, wadda ya think, 2 mil inserts a day, TRIGGER? or Derived Column?|||Looks like a pretty easy kill, but if you have a test server, this will definitely need to be tested. Even if you don't have atest server, this should be tested. Try replacing all four updates with this:

UPDATE
Envox_CallLog
SET StartTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallStartTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallStartTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallStartTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallStartTime)
else CallStartTime end, -- covers EST
EndTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallEndTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallEndTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallEndTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallEndTime)
else CallendTime end,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE Adjusted=0 AND Completed=1

It will save you 3 go rounds on the table, which may translate to quartering the run time. Even if you get half an improvement, it should be good.|||Still gonna scan

But there will a perf boost...you change the old and new queries into selects to see what the return|||That's interesting. Why are the times be logged as different zones?

The server should be using the default time of the server.

How are you getting different time zones for the calls? This has to be a web app with 2 million hits a day, can't be distributed...

That's very curious how it's artichected...

Can you describe how the users us the app?|||OHIO! Blind dude, get over there and help him out...
Be happy to. My company is located in Columbus, and one of the services we offer is DBA outsourcing. I have a client that used to use a full-time dba, but we now cover their requirements with less than 10 hours per week.
If your company is interested in either consulting or dba support, just let me know.|||I doubt they have 2 mil inserts a day, but I could be wrong. Certainly not to a single table, I hope. You are right that this is bound to scan. You may be able to put an index on the Adjusted column. It is just a tad counterintuitive, but if the statistics are kept up to date, the optimizer should see only a very few rows with Adjusted=0, against tons with Adjusted = 1

After that, the first update has to be re-written to say something along the lines of

AND StartTime between dateadd (mi, 200, StartTimeAdj) and dateadd (mi, -200, StartTimeAdj)

provided there is an index on StartTime. That should be easy to switch around if the index is actually on StartTimeAdj.

I will have to think about the trigger vs. Derived Column. I heard somewhere that Computed Columns are not actually stored, but computed on each use. Not sure that is right, though.|||BOL has the following. That would be some heck of a computed column though. I wonder if a compouted can use an index? If it does it would help out with the zip problem.

computed_column_expression

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.

Note Each row in a table can have different values for columns involved in a computed column, therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.|||The update statement seems to imply that each Invoice goes to a single JobID (sounds normal), but each Invoice has only a single property?

As for why the multiple Time Zones, who knows what business model decided this was a good idea. Seems that all calls are "synchronized" using the original invoice's zip code, so no fair going to the next time zone and calling in about your invoice.

I wonder how they handle Indiana and Arizona during Daylight Savings Time.|||I wonder if a compouted can use an index? If it does it would help out with the zip problem.Did you read what you posted from BOL ?!?!

If I were going to implement on multiple servers (or there was any chance that I might someday want to do so), I'd probably use UCT/GMT to store DATETIME values instead of local time. Using GUIDs and UCT are two key points I've found to keeping things simple as you scale up.

-PatP|||Awesome, awesome lots of feedback. I just returned from lunch so I haven't tried anything out yet, but I thought I'd post back what I could.

I can't get too technical, but our company does mystery shopping. What we're looking at right is obviously a stored procedure which converts times for different time zones.

We have at home employees that will call into our Envox Telephony server to pick up a job and then once connected there routed through some processes and an external number is dialed and they're connected to whom they're supposed to talking to. All the while their calls are recorded and the times they called are logged. Because the table is using time stamps, all the times are in the local time zone. This SProc, I presume is being used to convert all the times into the actual time for the call destination.

So if the employee was routed to California for their call, then the local time is converted to whatever time is would have been in Cali at the time of the call. Then is for reporting purposes obviously.

We have automated VB apps in place that run throughout the day doing different processes - this Sproc gets called from that automated app probably every 30 minutes or so. So roughly 48 times a day.

Name Rows Reserved Data Index Unused
Envox_CallLog 164627 30968 KB 16600 KB 88 KB 14280 KB
Properties 68628 131880 KB 82984 KB 10040 KB 38856 KB
Invoices 718950 3575416 KB 2871416 KB 165576 KB 538424 KB
ZipCodes 42567 5200 KB 5168 KB 16 KB 16 KB|||Computed columns can be indexed. I had to do it when a guy tried to join a varchar column to an int column. Not pretty, when the table was getting 2 - 300 lookups per second.

So, add a column to properties as left(zip, 5). So much for the international market, I guess. But then, that was a debate all to itself in the design forum, if I recall.|||Continuing on - Yes each invoices has a single JobID and single Property as each of our Jobs are placed on one property at a time. If we need to do more than one property, we do more than one job.

2 mil inserts a day? Not even. We've been going for 3 or 4 years now and we're upto 800,000 Jobs roughly, but there IS alot of activity going on. I should do some queries on how many tables and things we have and what kind of traffic is in and out of the SQL server all day. 2 mil inserts is probably a stretch. But I could bet we hit 500,000 transactions easy.|||It's all index seek baby

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([Zip+4] char(10), Zip AS LEFT([Zip+4],5))
GO

SET ARITHABORT ON
GO

CREATE INDEX IX1 ON myTable99(Zip)
GO

INSERT INTO myTable99([Zip+4])
SELECT '07052-0000' UNION ALL
SELECT '12345-1234' UNION ALL
SELECT '00000-0000'
GO

--[CTRL]+K

SELECT *
FROM myTable99
WHERE Zip LIKE '0%'
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

First thing I do is add a computed column zip in your properties table..it will have 0 impact to your application, and has the potential to speed up this existing query by changing it to reference that column.

That's a low impact/ high reward fix.|||Those tables look awfully lean on indexes (based on index size). Can you run sp_helpindex on each of those tables? Looks like this is coming down to design of the application, and how you can mitigate some of the bigger flaws. The tables are not huge, but as Brett points out, the scans are killing you.|||Well your scanning properties with 68,000 rows, and the zip fix I posted will fix that.

Now post the DDL, make sure you included all the contraints and indexes

Let me ask this, do you have a dev server where you can test these chnages out? Do you have the authority to ALTER database objetcs, like add a computed column?|||Okay, I'm a horrible typist so please forgive all the missed words and etc. I'll try to clean up my act a little.

Everything we do is on a testing server first and moved "live" once we're confident it's working as it should. This goes from IIS to SQL Server to Access to whatever. I do have permissions on the Database to do anything I need to do. I can do all of this stuff on the testing server, but I would have to go over the changes with the rest of the IT staff before I moved anything into production.

Let me run the HelpIndex SP and post back the findings.|||Envox_CallLog
PK_Envox_CallLog clustered, unique, primary key located on PRIMARY Call_ID

Properties
PK_Properties clustered, unique, primary key located on PRIMARY PropID
Properties15 nonclustered located on PRIMARY PropID, Property Name
Properties29 nonclustered located on PRIMARY PropID, ZIP, Country
Properties40 nonclustered located on PRIMARY PropID, ClientID
Properties41 nonclustered located on PRIMARY ClientID

Invoices
Invoices16 nonclustered located on PRIMARY ShopID
Invoices23 nonclustered located on PRIMARY ClientID, RcvdDate, AForm
Invoices31 nonclustered located on PRIMARY PropID, ShopID, ClientID
Invoices32 nonclustered located on PRIMARY ClientID, RcvdDate
Invoices34 nonclustered located on PRIMARY FU_Expires
Invoices35 nonclustered located on PRIMARY Select_Shop_Date
Invoices36 nonclustered located on PRIMARY FU_PDFed
Invoices38 nonclustered located on PRIMARY PropID
Invoices39 nonclustered located on PRIMARY ClientID
PK_Invoices clustered, unique, primary key located on PRIMARY JOBID

ZipCodes does not contain any Indexes.|||These are useless, you have 2 other indexes that would take care of that

Invoices38 nonclustered located on PRIMARY PropID
Invoices39 nonclustered located on PRIMARY ClientID

And you need a zip code index on the computed column like I posted.

Now what about the DDL|||I am surprised that the ZipCodes table has no indexes. I should think they would put a PK on the Zip column. Heck, add time_zone, and you have a covering index.

Just for curiosity, the Envox system is not in your control, but is the database? I know some vendors get awful uppity, when you start pointing out where indexes should be, and how table designs could be improved, etc.. They can even say your support contract is suspended for such things. Not a nice place to be caught when the fecal material is introduced into the atmospheric circulation machinery, if you know what I mean.|||Alter Table Properties
Add Zip5 as left(Zip,5)

I did that to add the computed column to the Properties table. Hopefully that was right. I then added an index to that column as requested. Running the old SProc as is, it was 17 seconds this time around. Using the new SProc which was altered to join on the new computed column instead of the old column

CREATE PROCEDURE [dbo].[Envox_AdjustCallLog2] AS

UPDATE
Envox_CallLog
SET StartTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallStartTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallStartTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallStartTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallStartTime)
else CallStartTime end, -- covers EST
EndTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallEndTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallEndTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallEndTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallEndTime)
else CallendTime end,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON Properties.Zip5 = ZipCodes.Zip
WHERE Adjusted=0 AND Completed=1
GO

I got a run time of 2 seconds on the first run. However I'm conerned now. The old SProc affected 208 rows in total. The new one was 256 or so. It's like the old SProc is adjusting rows back the original time and then fixing them again.

Brett. I'm sorry, but can you explain to me what the DDL is? I think I missed that.|||Actually the Envox server is in house and we do control it, I just don't mess with it personally as I'm clueless when it comes to that sort of thing. However, I've given our top IT guy the link to this thread and he also handles the Envox server and he said this had been an interesting read and he would have no problems updating the SPRoc and adjust things as needed if it mean performance gains. So we're in the clear on that.|||For the DDL, look at the bottom in the FAQ section of this link here

http://www.dbforums.com/t1196943.html

As for as to why, you have to remeber the top part of the code

This

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

Sets the population to be affected in the subsequent DML. So the population is always changing...if you didn't do this you would get zero rows affected, unless soemthing else is modifying the Adjusted column.

Again, in my opinion, this is a poor way to run a batch window process.

I still think that you can not only have a computed column for the zip code (yeah for 2 seconds), but I think you could turn the adjusted time column in to a computed column, and then just eliminate this entire process altogether.

See, the problem with storing derived/data and storing it, is usually as soon as it's computed it becomes stale, and potentially out of synch with the real data.

No a computed column is derived at the time it's requested, so it's "fresh out of the oven"

Also, if the computed column doesn't work (there may be some issues with sub queries), there's nothing to say that this couldn't be a view.

Same concept but more flexibility.

I would hazard to say the majority of us don't use computed columns, so thanks for the exercise.|||You are talking about a computed column to strip off the basic five digit zip code?
You should store the zip and the zip4 separately to begin with. Or add a zip5 column and populate it on insert via a sproc or trigger, since it is probably not volatile data.|||Another thing to watch for, is if the insert into the property table uses one of my <sarcastic>favorite</sarcastic> constructs the insert into with no field list. Adding the computed field or any field would break that insert. Just one more thing to check.|||aye yup...so much for the low impact solution...I guess you could check by looking for

WHERE ROUTINE_DEFINITION NOT LIKE 'INTO%('
OR ROUTINE_DEFINITION NOT LIKE 'INSERT%('

But that wouldn't take into account code based sql

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(Col1 int, Col2 int)
GO

INSERT INTO myTable99
SELECT 1,2
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Col3 int
GO

INSERT INTO myTable99
SELECT 1,2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||I tried posting earlier but I was having trouble accessing dbforums. I'll post up the DDL when I have time, but I've been really busy with meetings and stuff today. We're having a DBA come (Wohoo!!) to help us out and get us back on the right track.

Thanks to everyone who posted ideas and suggestions as it's helped me tremendously to better understand how SQL Server operates and things I can do to fine tune it. I still have much to learn!

To get back on topic, I'm 99.9% certain we don't have any Inserts in any of our code that does not use a field list. Actually, when I mentioned that to the IT guys a couple of them didn't even know you could do that, so I think we're ok there with the computed column.

I do agree the zip and +4 should have been store seperately, but I chalk that up to the programmers learning as they want and just adding stuff over the years. Like I said we're 4 years in and there are so many things that we'd like to fix. But when 80% of our operation is automated, it's tricky to just go in and change things.

Like for instance for EVERY client we have, we have 3 tables. All 3 tables are identical except for the name and the fact that each table holds the info for the client at different stages of the process. Clearly we should of had 1 table and just an additional flag that could be used to differentiate the data at different stages. But then how do you change all the processes that we have in place that rely on all those tables. Aye!

We need and overhaul badly.|||Congratulations on getting some relief in there. Take notes, on what he checks. Sounds like there may be another few stored procedures that will need a bit of tuning, but it is a whole lot easier to detect such problems when you are sitting in front of Profiler and Query Analyzer.

And thanks for being a good poster. Too many times we get "I have problem pls help" posts that we spend more time pulling the guy's teeth looking for clues as to why their hamster died.

Help with query

Suppose that I have a table that represents the chain-of-command hierarchy
of a company. Lets say that the first column of the table represents the
employee name and the other represents the employee's boss. Lets also assume
that one employee can have several bosses.
What I would like to do is to create some kind of query that will return the
company's Boss hierarchy. For example, lets pretend that my table has the
following information.
Worker Boss
-- --
JoeCacas Dumb
JoeCacas Dumber
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
BigDaddy Lordy
In this case, if I asked the query to tell me the Boss hierarchy of JoeCacas
I would expect the query to first search for all the bosses of JoeCacas
which will return the records:
-- 1 Level--
JoeCacas Dumb
JoeCacas Dumber
Then the query will need to search for the bosses of Dumb and Dumber which
will return the following:
-- 2 Level--
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
Finally, the query will return the bosses of MadDog, BidDaddy and BigCheese.
Since the only person that has a boss is BigDaddy then the result will be:
-- 3 Level--
BigDaddy Lordy
The result that I would expect would be:
JoeCacas Dumb
JoeCacas Dummber
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
BigDaddy Lordy
I think this can be done with some kind of recursive query. Is this
possible? Thank you.Hi Rene,
You can create a UDF to achieve the task.
Basically, iterate through the levels going upwards:
USE tempdb;
SET NOCOUNT ON;
GO
CREATE TABLE EmpsHier
(
worker VARCHAR(20) NOT NULL,
boss VARCHAR(20) NOT NULL,
PRIMARY KEY(worker, boss)
);
INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
GO
CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
@.MgmtChain TABLE
(
worker VARCHAR(20),
boss VARCHAR(20),
lvl INT
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.MgmtChain
SELECT worker, boss, @.lvl FROM EmpsHier
WHERE worker = @.emp;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.MgmtChain
SELECT P.worker, P.boss, @.lvl
FROM EmpsHier AS P
JOIN @.MgmtChain AS C
ON C.lvl = @.lvl - 1
AND C.boss = P.worker;
END
RETURN;
END
GO
SELECT * FROM fn_mgmtchain('JoeCacas');
worker boss lvl
-- -- --
JoeCacas Dumb 0
JoeCacas Dumber 0
Dumb MadDog 1
Dumber BigCheese 1
Dumber BigDaddy 1
BigDaddy Lordy 2
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rene" <nospam@.nospam.com> wrote in message
news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
> Suppose that I have a table that represents the chain-of-command hierarchy
> of a company. Lets say that the first column of the table represents the
> employee name and the other represents the employee's boss. Lets also
> assume that one employee can have several bosses.
> What I would like to do is to create some kind of query that will return
> the company's Boss hierarchy. For example, lets pretend that my table has
> the following information.
> Worker Boss
> -- --
> JoeCacas Dumb
> JoeCacas Dumber
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> BigDaddy Lordy
>
> In this case, if I asked the query to tell me the Boss hierarchy of
> JoeCacas
> I would expect the query to first search for all the bosses of JoeCacas
> which will return the records:
> -- 1 Level--
> JoeCacas Dumb
> JoeCacas Dumber
> Then the query will need to search for the bosses of Dumb and Dumber which
> will return the following:
> -- 2 Level--
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> Finally, the query will return the bosses of MadDog, BidDaddy and
> BigCheese.
> Since the only person that has a boss is BigDaddy then the result will be:
> -- 3 Level--
> BigDaddy Lordy
>
> The result that I would expect would be:
> JoeCacas Dumb
> JoeCacas Dummber
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> BigDaddy Lordy
> I think this can be done with some kind of recursive query. Is this
> possible? Thank you.
>|||Hi
you can also check for this:
http://support.microsoft.com/defaul...kb;en-us;248915
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Itzik Ben-Gan" wrote:

> Hi Rene,
> You can create a UDF to achieve the task.
> Basically, iterate through the levels going upwards:
> USE tempdb;
> SET NOCOUNT ON;
> GO
> CREATE TABLE EmpsHier
> (
> worker VARCHAR(20) NOT NULL,
> boss VARCHAR(20) NOT NULL,
> PRIMARY KEY(worker, boss)
> );
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
> INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
> INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
> GO
> CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
> @.MgmtChain TABLE
> (
> worker VARCHAR(20),
> boss VARCHAR(20),
> lvl INT
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.MgmtChain
> SELECT worker, boss, @.lvl FROM EmpsHier
> WHERE worker = @.emp;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.MgmtChain
> SELECT P.worker, P.boss, @.lvl
> FROM EmpsHier AS P
> JOIN @.MgmtChain AS C
> ON C.lvl = @.lvl - 1
> AND C.boss = P.worker;
> END
> RETURN;
> END
> GO
> SELECT * FROM fn_mgmtchain('JoeCacas');
> worker boss lvl
> -- -- --
> JoeCacas Dumb 0
> JoeCacas Dumber 0
> Dumb MadDog 1
> Dumber BigCheese 1
> Dumber BigDaddy 1
> BigDaddy Lordy 2
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>|||Chandra,
The example you provided expands/explodes a node (returning descendants down
the digraph). Rene asked for ancestors (up the digraph).
BG, SQL Server MVP
www.SolidQualityLearning.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:7E732409-B2C7-4B00-B986-3A85B5098E65@.microsoft.com...
> Hi
> you can also check for this:
> http://support.microsoft.com/defaul...kb;en-us;248915
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Itzik Ben-Gan" wrote:
>|||Thanks Itsik, that was awesome! I pretty much copied and pasted your code
and everything worked great.
It took me a little bit to figure out what you did but now I understand it.
Thanks again.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uiI%23HwNYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Chandra,
> The example you provided expands/explodes a node (returning descendants
> down the digraph). Rene asked for ancestors (up the digraph).
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:7E732409-B2C7-4B00-B986-3A85B5098E65@.microsoft.com...
>|||I was just thinking. What if multiple users call the function at the same
time? Will they all be modifying the same table at the same time screwing up
the result? If this is the case, is there a way to create a unique table
every time the function is called?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hi Rene,
> You can create a UDF to achieve the task.
> Basically, iterate through the levels going upwards:
> USE tempdb;
> SET NOCOUNT ON;
> GO
> CREATE TABLE EmpsHier
> (
> worker VARCHAR(20) NOT NULL,
> boss VARCHAR(20) NOT NULL,
> PRIMARY KEY(worker, boss)
> );
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
> INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
> INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
> GO
> CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
> @.MgmtChain TABLE
> (
> worker VARCHAR(20),
> boss VARCHAR(20),
> lvl INT
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.MgmtChain
> SELECT worker, boss, @.lvl FROM EmpsHier
> WHERE worker = @.emp;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.MgmtChain
> SELECT P.worker, P.boss, @.lvl
> FROM EmpsHier AS P
> JOIN @.MgmtChain AS C
> ON C.lvl = @.lvl - 1
> AND C.boss = P.worker;
> END
> RETURN;
> END
> GO
> SELECT * FROM fn_mgmtchain('JoeCacas');
> worker boss lvl
> -- -- --
> JoeCacas Dumb 0
> JoeCacas Dumber 0
> Dumb MadDog 1
> Dumber BigCheese 1
> Dumber BigDaddy 1
> BigDaddy Lordy 2
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||I am assuming that the @.MgmtChain TABLE is created on the tempDB database,
although I have a feeling tempDB is not used at all here. Just checking.
"Rene" <nospam@.nospam.com> wrote in message
news:OGy$fTXYFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I was just thinking. What if multiple users call the function at the same
>time? Will they all be modifying the same table at the same time screwing
>up the result? If this is the case, is there a way to create a unique table
>every time the function is called?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
>|||>> the chain-of-command hierarchy of a company. Lets say that the first colu
mn of the table represents the employee name and the other represents the em
ployee's boss. Lets also assume that one employee can have several bosses. <
<
Several bosses? Isn' t that like having a circle has corners? Look
up the definition of a hierarchy. If you really wanted a hierarchy,
then get a copy of TREES & HIERARCHIES IN SQL for several
non-procedural, non-recursive ways to model this which will run orders
of magnitude faster than a recursive proc or a loop.|||> Several bosses? Isn' t that like having a circle has corners?
I don't have such table. It was just an example (analogy) that I came up
with to try to make it easier to get my point across. Hierarchy may not be
the most proper word to use but it I figured it would help explain what I
needed.|||The table is logically a variable local to the batch. No conflicts with
other invocations.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rene" <nospam@.nospam.com> wrote in message
news:OGy$fTXYFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I was just thinking. What if multiple users call the function at the same
>time? Will they all be modifying the same table at the same time screwing
>up the result? If this is the case, is there a way to create a unique table
>every time the function is called?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
>

Help with query

Hi all,
I have the following table:
CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](100) NOT NULL,
[CompanyTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL)
These companies can have a variety of CompanyTypeID's. I want to
retrieve a different number of records for each CompanyTypeID in one
call.
Ex.:
DECLARE @.countType1 int
DECLARE @.countType2 int
DECLARE @.countType3 int
DECLARE @.countType4 int
DECLARE @.countType5 int
SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
UNION
SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
UNION
SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
UNION
SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
At this point, Im doing it all with a UNION and getting what I want.
IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
HAVING and use the @.count variables to return the desired number of
records per companytype.
Thanks,
VictorWhat's wrong with using the union?
you'll want to change it to a UNION ALL in the interim, though, since
there won't be any duplicates to eliminate.
ViLo wrote:
> Hi all,
>
> I have the following table:
> CREATE TABLE [dbo].[Company](
> [CompanyID] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](100) NOT NULL,
> [CompanyTypeID] [int] NOT NULL,
> [Active] [bit] NOT NULL)
>
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
>
> Thanks,
> Victor
>|||ViLo (victor@.lamovidarecords.com) writes:
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
First of all, your query looks funny, as you say TOP 100 but as
there is no ORDER BY, you will get the 100, 200, etc as the optimizer
sees fit. Which will be neither random, nor deterministic.
If you are on SQL 2005 you can use TOP (@.count) if the arbitrary
choice is OK to you.
On SQL 2000, you probably need to use a temp table, particularly if you
want the TOP 200 by alphabet or so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

help with LIKE syntax...

HI All,

I am writing some code where i want to get a list of all the company names that start with say, M...

here is what i have:

Dim M_company, companyID_M
Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )

but this is only returning the first company in the list that starts with M. Can anybody help with getting it to return more than one value?

thanks,
Leissa1. this is a 'classic' ASP question. this is an ASP.NET forum.
2. that code is doing exactly what I'd expect it to
3. you've neglected to loop through the returned recordset. are you new at this by any chance?|||hmm...yes i am... how'd you guess? :)

so i need to loop something to make it read all the records? any help on how i can do this?|||couple of things :

1. take a decent beginner's tutorial. try www.asp101.com, www.aspin.com, www.4guysfromrolla.com - it'll fill you in on more than a simple forum post.
2. use a classic ASP forum - the ASP.NET snobs here will either ignore your question or tell you you're behind the times (they're wrong, but hey). try www.aspmessageboard.com - you'll usually find me there answering questions.

it's like this

While not recordset.EOF
 ' do stuff here
recordset.moveNext()
Wend

|||Thanks for your help in pointing me in the right direction... :)

i am using the while not statement like this:

Dim M_company, companyID_M, allRecords
' create a recordset
Set allRecords = Server.CreateObject("ADODB.Recordset")
While NOT allRecords.EOF
Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )
allRecords.MoveNext
WEnd

however i get this error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/spiritxmas/displayExhibList_M.asp, line 17

Any quick fix? or should i just start over with the tutorials...

thanks,

Leissa

Help with Join

H
I have two tables
Create table company
( firm char(32),
p_name char(40),
proj_code char(8),
proj_start_dt datetime
)
go
insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
go
Create table employee
( firm char(32),
p_name char(40),
proj_end_dt datetime
)
go
insert into employee values ("ABC","John Doe", "Nov 20 2003")
insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
go
I need to extract data for p_name with proj_start_dt > proj_end_dt
my query is not working
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns all values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC John Doe
DJOE Nov 12 2001 12:00AM
ABC Jane Doe
JANEDOE Oct 26 2000 12:00AM
ABC Bruce Smith
BRUCES Mar 1 2002 12:00AM
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c,
employee e
where c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns no values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
Could someone please help.
AjmisterThanks for posting the DDL and sample data. Could you show and explain
exactly what result you want. As far as I can see there are no rows where
Proj_start_dt is greater than Proj_end_dt for any given P_name.
David Portas
SQL Server MVP
--|||try this...
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
where c.proj_start_dt > e.proj_end_dt
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Did you try it? :-)
David Portas
SQL Server MVP
--|||Sorry abt my previous post, that was wrong.
here is the answer,
the company table has the proj_start_dt = Apr 15 2004 which is greater than
the proj_end date.But the value of p_name in the company table is Lisa Cox
which is not there in the employee table.So when you do a join you will not
get the result.
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Your first join is returning all rows because you are doing an outer join
which is telling SQL you want to return all row from the set on the left
(company) even if they don't match any rows in the set on the right
(employee). If you run the following command you can see you are not
returning any values from the employee table:
select c.*, e.*
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
Your second query seems to be doing what you want, and you are not getting
any rows returned because the are no company.proj_start_dt's that are > the
employee.proj_end_dt when the company.p_name and employee.p_name are equal.
Hope this helps explain why you are getting the results you posted.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ajmister" <ajmister@.optonline.net> wrote in message
news:etG3OUXCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>|||Thank you for a quick response, sorry I want to extract all Proj_start_dt
that do not have a proj_end_dt
Thanx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:taednVcaFv9azpzfRVn-sg@.giganews.com...
> Thanks for posting the DDL and sample data. Could you show and explain
> exactly what result you want. As far as I can see there are no rows where
> Proj_start_dt is greater than Proj_end_dt for any given P_name.
> --
> David Portas
> SQL Server MVP
> --
>|||Yes, but it did not return any values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
I would the query to return proj_start_dt whihc do not have proj_end_dt
(sorry about the previous error)
example
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
Thank you
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:yt6dnXskg5uRy5zfRVn-vg@.giganews.com...
> Did you try it? :-)
> --
> David Portas
> SQL Server MVP
> --
>|||Try this:
SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
FROM company AS C
LEFT JOIN employee AS E
ON C.p_name = E.p_name
AND C.proj_start_dt < E.proj_end_dt
WHERE C.proj_start_dt IS NOT NULL
AND E.proj_end_dt IS NULL
David Portas
SQL Server MVP
--|||Thank you sir. That gave me the correct output.
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OIydnb5UiOmFGZzfRVn-iA@.giganews.com...
> Try this:
> SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
> FROM company AS C
> LEFT JOIN employee AS E
> ON C.p_name = E.p_name
> AND C.proj_start_dt < E.proj_end_dt
> WHERE C.proj_start_dt IS NOT NULL
> AND E.proj_end_dt IS NULL
> --
> David Portas
> SQL Server MVP
> --
>