|
Hi all ,
Iam Using a query which returns a bulk of data.
first my query contains sub query to find the count so i panned to change as a function ..
So i created a function which returns a table..
create function fnname
returns table(name as varchar,....,....,...,...,...,...)
I inserted the values into the temp table from the select query and
(i want to find the count of receipt numbers so ...
i updated the query with another query update temp table set rno =(select count(rno) from table where
....)
then i executed the function but it is quite slow
please suggest me any way to increase the speed of the function or query..
how to increase the speed of the function
|
|
|
|
|
cbenan wrote: sub query
Have you tried a join?
|
|
|
|
|
It is unikely that the use of the function will affect the speed, it is more likely to be the number of times you call it. Quite often you can change the performance by totally restructuring your query. Break it down to part and do the part that reduces your data volume the most first.
Try using the execution plan to identify table scans that do not use indexes.
I have found the using table vars to hold large volumes is a disaster - I have gone back to #temp tables for anything over 2k rows. I also have no compunction about throwing an index onto a temp table.
If you have 2005 then you may want to look at rownumber and partitioning in a query to get the count by invoices.
Your question does not have 1 answer, this is where science turns into art, good luck!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<ok thank="" u="" very="" much="" for="" ur="" reply="">
|
|
|
|
|
Typically if you break down a single query to multiple different operations using a function or a procedure, the result can be slower. Because if you have separate SQL statements there's no way for the optimizer to search for the best plan to the whole query, only for the subcomponents.
Also if you use temp tables and update and select them, you'll most likely cause physical I/O which may/may not be prevented when using a single query.
Sometimes it's just impossible to write a single query for a certain task, but if you do have the opportunity, you should test the results using this approach (create a single query, combine few operations thus eliminating intermediate steps etc).
Another thing is indexing. Make sure that you have adequate indexes and on the righ columns (or column combinations).
For further details, I think you should post the code you currently have.
|
|
|
|
|
i am bit confused about my question only please help me to get the result
i hav 3 tables
table1,table2,table3
table1
uid shiftcode startdate enddate
100 s1 xxxxxx xxxxx
101 s1 xxxxxx xxxxx
102 s1 xxxxxx xxxxx
table2
uid patterncode
103 p1
table3
uid shiftcode patterncode
103 s2 p1
i want shiftcode's of 100 to 103 users
how to get
plzz help me....
icanmakeiteasy
|
|
|
|
|
I think your question is lacking some information. Could you explain the logic of combining those rows together. Is it for example that when table1 has shiftcode s1 you want shiftcode s2 from table3 or is the idea to go through table2 and join patterncode, but then again how does pattern code relate to shiftcode in table1 etc.
|
|
|
|
|
thx for reply...
user 103 is on bench so his shift code is s2 or any other
if user got work for some days simply he moves to table1 with his working days and his work code(s1,s2,s3..etc)
just i want work code(shiftcode) of 100 to 103 users between given dates
icanmakeiteasy
modified on Tuesday, February 17, 2009 8:18 AM
|
|
|
|
|
So did I understand correctly, table1 contains info about users andtheir work periods and you want to find users having work in certain period. If that's the case, could it be something like
select ...
from table1
where startdate between periodstart and periodend
or enddate between periodstart and periodend
where the periodstart and periodend are the dates you're interested in
|
|
|
|
|
thx for reply...
srry... i am mistaken
table3 does not have uid column
table3
shiftcode patterncode
s2 p1
i want the shift code of users (100 to 103) between given periodic times
by using given query i can not get the shift code of user 103 where does not exist in the table1, so i have to get the shift code from table3, here table3 having patterncode only, now i have to get the paaterncode from table2... using that i want to show the shiftcode of user 103....
am i clear now??
icanmakeiteasy
|
|
|
|
|
it is very simple use joins and co related sub queries
|
|
|
|
|
i am little poor in joins and sub queries
can u plzz give me some idea
icanmakeiteasy
|
|
|
|
|
i got solved this problem
[^] from this forum
icanmakeiteasy
|
|
|
|
|
Merci Ashfield.
I used your solution :
SELECT ID_STUDENT, CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,<br />
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar,<br />
[122]) END AS note3,<br />
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3<br />
<br />
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE, CODE<br />
FROM EXAMEN) p PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt<br />
<br />
<br />
I have not the problem of data type but I have the data displayed by double
Here are my data TABLE examen
Id_student---- id_feild ---- note----code
9306-------------121 ----------- 0-------1
9306-------------122---- -------4-------0
9306------------123----------- 12----- 0
9307------------121 ------------3------ 0
9307------------122------------ 0------ 1
9307------------123 ------------9------ 0
9308------------121------------ 12------0
9308------------122------------ 4------ 0
9308------------123------------ 9------0
following the requet résultatt
9306----NULL----NULL----NULL
9307----NULL----NULL----NULL
9308----NULL----NULL-----NULL
9306----NULL----4-------12
9307-----3------NULL----9
9308-----12-----4------9
9306-----ASD----ASD-----ASD
9307-----ASD----ASD-----ASD
and this is what I want
id_student---note1----note2---note3
9306---------ASD-------4---------12
9307---------3-------ASD -------9
9308--------12------4---- ------9
I dont know why please help me.
Thanks
modified on Tuesday, February 17, 2009 6:32 AM
|
|
|
|
|
Hi!
I think I have this result because we must specify
that the ID_FIELD= 121 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
and ID_FIELD=122 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [122]) END AS note2
how ? the case when accepts two conditions?
thanks!
|
|
|
|
|
I don't if I follow the logic but if you drop away the code in pivot like this:
SELECT *
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt
you will get a result as wanted where note is 0 for the rows where code is 1 (is this always true?):
ID_STUDENT 121 122 123
---------- --- --- ---
9306 0 4 12
9307 3 0 9
9308 12 4 9
Now if you replace the zeros like:
SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt
you will get:
ID_STUDENT note1 note2 note3
---------- ----- ----- -----
9306 abs 4 12
9307 3 ASD 9
9308 12 4 9
Is this getting you any closer to the result?
|
|
|
|
|
Thank you very much Mika Wendelius you helped me a lot in this site  .
for me code = 1 means that the student is abcent so his note= 0 and I have to replace 0 by 'abc' to differentiate between a student absent and another who is present and received 0.why I used code code_absence.
I do not know if I come to explain my problem.
Thnaks.
|
|
|
|
|
It didn't come out very pretty but could you use this for somekind of starting point. The idea is that the first query gets the records where the student is absent and the second gets those where the student isn't absent. then these are joined and columns are combined:
SELECT coalesce(a.ID_STUDENT, b.ID_STUDENT) as id_student,
coalesce(a.note1, b.note1) as note1,
coalesce(a.note2, b.note2) as note2,
coalesce(a.note3, b.note3) as note3
FROM
(SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 1) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) a
full outer join
(SELECT ID_STUDENT,
CONVERT(varchar, [121]) AS note1,
CONVERT(varchar, [122]) AS note2,
CONVERT(varchar, [123]) AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 0) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) b
on a.ID_STUDENT = b.ID_STUDENT
The result is:
id_student note1 note2 note3
---------- ----- ----- -----
9306 Abs 4 12
9307 3 Abs 9
9308 12 4 9
|
|
|
|
|
Hi.
Thank you a lot ,merci beaucoup Mika Wendelius it works .
|
|
|
|
|
You're welcome
|
|
|
|
|
Not sure if this is the location where I should post such a question (if not please direct me to the proper forum), but I am designing a report in VS2005 and need to have a date field display blank or an empty string if the date is 1/1/1900. I have written the IIf statement as such:
=IIF(Fields!seStartDate.Value = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")
, however I am getting a syntax error. Please advise as to how this should be written...thanks in advance to all that reply.
|
|
|
|
|
Just some hints as I don't have the chance to test it. Try the following:
=IIF(Format(Fields!seStartDate.Value, "MM/d/yyyy") = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")
May be you want to try this as well:
=IIF(Fields!seStartDate.Value.ToString() = '1/1/1900', '', Format(Fields!seStartDate.Value, "MM/d/yyyy")
I am not sure whether you should us the double quotes (") within the format function or the single quotes for = '1/1/1900'.
Good luck,
Florian
|
|
|
|
|
Suppose we have a table like this:
CREATE TABLE Slots (
SlotNumber INT NOT NULL,
...,
PRIMARY KEY(SlotNumber)
);In this table, a maximum of X slots can be inserted. Given a table with Y rows in it, what is the next available slot?
One solution is to keep a second table with all the slots in it:
CREATE TABLE AllSlots (
SlotNumber INT NOT NULL,
PRIMARY KEY(SlotNumber)
);Then I could do something like
SELECT SlotNumber
FROM AllSlots
WHERE SlotNumber NOT IN (SELECT SlotNumber FROM Slots)
Does anybody have a better solution? Note that I have only ANSI SQL in my arsenal (no fancy TSQL/PL1/Whatever constructs are at my disposal)
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
Just wondering, why don't you simply take MAX(SlotNumber) + 1 (if you used that, I wouldn't recommend to use the SlotNumber as primary key, but as a unique key and define a different primary key).
If the MAX + 1 is less than maximum amount of slots, you still have a free slot.
If the slots are not filled in order, I think your solution is fine. Just when querying, you should take top 1 row if you need only one empty slot.
modified on Monday, February 16, 2009 1:27 PM
|
|
|
|
|
I forgot to mention that there's an upper and lower limit of the slots. The limits may be 1 and 999 for example. Essentially, the query would be "pick any number between 1 and 999, that isn't found in the table".
For small ranges such as [1, 999], it's probably easiest to select everything and order by the primary key (a no op, since the primary key index is sorted already), and then scan for the next pair of adjacent slots X and Y where X + 1 != Y. That means that X + 1 must be available. This is however not feasible if the slot span is large-ish, because it'll degrade to a table scan... 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
