July 15, 2008 at 7:05 am
Hi,
I am trying to create a report using asp/html calling a stored procedure to get the data.
One of the columns needed is gathered using this sql statement.
select distinct mn as "Month",s.name as "Name"
from flt f join mission m on f.mseq=m.mseq
join edt e on e.mseq = f.mseq
join species s on s.scicode = e.fish
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-APR-2007' and '31-mar-2008'
and left(m.mid,4)like 'BKS%'
GROUP BY mn,s.name
order by mn,s.name
and the result is
Month Fishery
1CLAMS
1CLAMS-STIMPSON SURF
1GROUNDFISH-GEN
1HALIBUT
1LOBSTER
1NDETERMINED
1SCALLOPS
1SEA URCHINS
1SILVER HAKE
1SNOW CRABS (QUEEN)
1TUNA
1UNDETERMINED FISHERY
2CLAMS
2CLAMS-STIMPSON SURF
2GROUNDFISH-GEN
2LOBSTER
2SCALLOPS
2SEA CUCUMBER
2SEA URCHINS
2SHRIMP/PRAWNS
2SILVER HAKE
2TUNA
2UNDETERMINED FISHERY
3CLAMS
3CLAMS-STIMPSON SURF
3CRABS
3GROUNDFISH-GEN
3HADDOCK
3LOBSTER
3NDETERMINED
3SCALLOPS
3SEALS
3SILVER HAKE
3UNDETERMINED FISHERY
4ARCTIC COD
4CLAMS
4CLAMS-STIMPSON SURF
4EEL POUTS
4EELS
4GROUNDFISH-GEN
4LOBSTER
4NDETERMINED
4SCALLOPS
4SHRIMP PANDALUS BORE
4SHRIMP/PRAWNS
4SILVER HAKE
4UNDETERMINED FISHERY
What I need is a column for each month with all of the fishery types for that month in one field.
Example;
Month Fishery
1 CLAMS,CLAMS-STIMPSON SURF,GROUNDFISH-GEN,HALIBUT,
LOBSTER,UNDETERMINED,SCALLOPS,SEA URCHINS,SILVER HAKE
SNOW CRABS (QUEEN),TUNA,UNDETERMINED FISHERY
Can this be done using a case statement or is there another way of accomplishing this?
Also, the query I displayed above takes about 37seconds to run, is there anything that you can suggest I change in order to speed this up a bit?
Any help that you can provide will most certainly be appreciated.
Thank you so much
Colleen
July 15, 2008 at 7:51 am
The concatenation problem I'll leave for someone else. As for the perf problem...
The functions you have on the column in the where clause prevent index seeks, so the query requires a full table/index scan to satisfy.
How many rows are in the table?
What's the table definition and what indexes does it have?
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
July 16, 2008 at 12:59 pm
GilaMonster (7/15/2008)
The concatenation problem I'll leave for someone else.
Concatanation should be easy, if you provide table structure and sample data.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 17, 2008 at 6:52 am
Hi,
Thank you both for your response. As for the processing speed.. unless I modify the database or table structure I don't believe I am going to be able to speed things up. The date is stored in three different fields. This is being looked into for future development.
As for the concatenation... I am using four tables to get the data.. Here is the query I am currently using with some sample data.
select distinct mn as "Month",s.name as "Name"
from flt f join mission m on m.mseq = f.mseq
join edt e on e.mseq = f.mseq
join species s on s.scicode = e.fish
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-APR-2007' and '31-mar-2008'
and left(m.mid,4)like 'BKS%'
GROUP BY mn,s.name
order by mn,s.name
and the result is
Month Fishery
1CLAMS
1CLAMS-STIMPSON SURF
1GROUNDFISH-GEN
1HALIBUT
2CLAMS
2CLAMS-STIMPSON SURF
2GROUNDFISH-GEN
2LOBSTER
3CLAMS-STIMPSON SURF
3CRABS
3GROUNDFISH-GEN
3HADDOCK
4SHRIMP/PRAWNS
4SILVER HAKE
4UNDETERMINED FISHERY
What I need is a column for each month with all of the fishery types for that month in one field.
Example;
Month Fishery
1 CLAMS,CLAMS-STIMPSON SURF,GROUNDFISH-GEN,HALIBUT
2 CLAMS,CLAMS-STIMPSON SURF,GROUNDFISH-GEN,LOBSTER
3 CLAMS-STIMPSON SURF,CRABS,GROUNDFISH-GEN,HADDOCK
4 SHRIMP/PRAWNS,SILVER HAKE,UNDETERMINED FISHERY
Table flt contains the date fields I need... all stored in separte fields
mn, dy, yr
I join the mission table in order to compare against a region.- mid
This table is then joined to edt by the primary keys of mseq.
Table edt is used simply to join the species table to get the name.
e.fish is the code
and then the species table is used as you can see to grab the name of the fish by the species code.
These tables have alot of fields in them so I just mentioned the ones that are relevant in this case.
In theory this seems to be an easy task but I am unable to get the species data into one field.
If you need further table or data information I can provide it.
I really appreciate your help. I am now on a very tight deadline and am struggling to meet it due to this small but very large issue. :crazy: :crying:
Anything at all that you can help me with would greatly be appreciated.
Thanks so much
Colleen
July 17, 2008 at 10:47 am
Ok, not really what I meant. Please take some time to read Jeff's article on how to post a question and data in order to facilitate someone's helping you.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 24, 2008 at 12:34 pm
Hi,
Thank you so much and I apologize for not getting back sooner, I got pulled away on a critical issue.
I did take the time to read the article and I do truly apologize for putting the post exactly the way he said not to... :blush:
Here is the code to create a table and enter test data into it.
create table TestTable
(Mth char(2),
Species char(30))
Insert into TestTable(Mth, Species)
select '1','Clams' UNION ALL
SELECT '1','SHRIMP' UNION ALL
SELECT '1','SCALLOPS' UNION ALL
SELECT '1','COD' UNION ALL
SELECT '2','LOBSTER' UNION ALL
SELECT '2','HALLIBUT' UNION ALL
SELECT '3','CRAB' UNION ALL
SELECT '3','SNOW CRAB' UNION ALL
SELECT '3','HERRING' UNION ALL
SELECT '3','PANDALUS BORE' UNION ALL
SELECT '4','SILVER HAKE' UNION ALL
SELECT '4','SEA CUCUMBER' UNION ALL
SELECT '4','SWORDFISH' UNION ALL
SELECT '4', 'TUNA'
I have tried a few things and still no success.
I hope this is a better post for you to read and for me to get the help I'm needing. As I've stated above I simply need to have a column for the month with the species concatenated into one field.
Example;
Month Species
1 Clams,SHRIMP,SCALLOPS,COD
Thanks for your patience and I do hope this is a much better way to post my problem.
Colleen
July 24, 2008 at 1:19 pm
Try this. The RTRIM is in there only because the species field is CHAR. You could omit it if you changed species to VARCHAR. Anyhow, just create the function and run it against the test data you provided, and it should give you what you are looking for. Make sure you add the end paren at my comment.
ALTER FUNCTION dbo.fnCSV_Fish
(
@mth CHAR(2)
)--There is supposed to be an end paren here, but I can't get it to show
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return + ', ','') + RTRIM(Species)
FROM TestTable
WHERE Mth = @mth
RETURN @Return
END
SELECT DISTINCT
mth,dbo.fnCSV_Fish(mth)
FROM testtable
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 5, 2008 at 9:23 am
Hi Greg...
Thanks so much for your help. I'm sure it would run perfectly if I could just create a function... :w00t:
With some analysis I believe it is due to the compatibility level set on my database.
I checked the properties of my database in the options section and the compatibility level is set at 65. In my research I see that it must be set to 80 in order to use or create functions. When I tried to change it to level 80 I get a message stating "Valid values of database compatibility level are 60,65,70." I have the option to select level 80 but this message is given when I press OK.
Do you have any ideas at all regarding this or another solution to my problem without using functions?
August 5, 2008 at 1:28 pm
I thought we lost you there. I am not sure if you were talking Greek up there, or Chinese math. I would suggest asking someone who can create functions to create it, if it is something you will need for a regular report. Other than that, I don't really have a solution, although I am sure some more knowledgable folks here can help you out. Sorry I can't be of more help.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 5, 2008 at 2:40 pm
Colleen (8/5/2008)
Hi Greg...Thanks so much for your help. I'm sure it would run perfectly if I could just create a function... :w00t:
With some analysis I believe it is due to the compatibility level set on my database.
I checked the properties of my database in the options section and the compatibility level is set at 65. In my research I see that it must be set to 80 in order to use or create functions. When I tried to change it to level 80 I get a message stating "Valid values of database compatibility level are 60,65,70." I have the option to select level 80 but this message is given when I press OK.
Do you have any ideas at all regarding this or another solution to my problem without using functions?
Open a query window (Query Analyzer) for this database and execute:
SELECT @@version;
It looks like you are using SQL Server 2000 client tools, but connecting to a SQL Server 7 instance. In which case, you would see the 80 compatibility in the drop-down - but will only be able to select up to 70 for the compatibility.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2008 at 2:48 pm
To help improve performance, you need to change how you are filtering:
select distinct mn as "Month",s.name as "Name"
from flt f join mission m on m.mseq = f.mseq
join edt e on e.mseq = f.mseq
join species s on s.scicode = e.fish
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-APR-2007' and '31-mar-2008'
and left(m.mid,4)like 'BKS%'
GROUP BY mn,s.name
order by mn,s.name
You can try the following and see if it improves the performance.
select distinct mn as "Month"
,s.name as "Name"
from flt f
join mission m on m.mseq = f.mseq
join edt e on e.mseq = f.mseq
join species s on s.scicode = e.fish
where (f.mn >= 4 and f.yr >= 2007)
and (f.mn <= 3 and f.yr <= 2008)
and left(m.mid, 4) like 'BKS%'
GROUP BY mn,s.name
order by mn,s.name
Notice, I removed the check on the day because it really is not necessary in this situation. This should allow indexes to be used on the f.mn and f.yr columns if they exist.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2008 at 5:16 am
Again thank you all so much.
I was on vacation for a little while... believe me, a well needed vacation... 😀
I do have the issue resolved... what I ended up doing was creating a stored procedure to do that same as the function would have, I then added the call to my asp page and put it through a loop. The function would have been a much cleaner way of coding but with that not being offered I had no other choice.
Thank you very much for all of your help and "Patience" they were greatly appreciated.
Colleen
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply