|
Ya,
I am agree with this point that you have rightly mentioned. Indexed views can be a better option. But , nested views also can create a performance issue.
Either you love IT or leave IT...
|
|
|
|
|
There exists at least one view that is faster than its equivalent simple query.
|
|
|
|
|
Please can you explain this
Thanks
|
|
|
|
|
Good evening
I have a table in database like following one
---------------------------------------------------------------------
rollNo | Name | Subjects
---------------------------------------------------------------------
101 | abc | english, maths, social science, science, hindi
102 | def | maths, social science, science
103 | ghi | english, social science, hindi
101 | jkl | english, maths, social science, science, hindi
----------------------------------------------------------------------
now i want to select only those records which have "science" in the "Subjects" column
so i use
Select * from tblStudent Where Subjects LIKE "%Science%"
but it also give the records that have "Social Science" ie record no 3
please help me
thanks in advance
modified on Saturday, May 2, 2009 8:40 AM
|
|
|
|
|
This is where you pay the price for BAD data design, If you were good you would create another table called Subject and link it to your student table in the correct foreign key data structure. Now you're screwed, you have to invent a work around for your crappy design.
Options Are:
Redesign you data structure correctly - this is the recommended solution.
Play around with the text structure (', Science') will get most except where it is the first subject.
Try and NOT like "Social Science"
Create a split function based on the "," and filter on the required value (most sql devs have a split function floating around in their tools set)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No doubt DB structure has million dollar worth butt still you can use
you can use In
"Select Col1 ,Col2 from tbl Where Col2 not In (SELECT Col2 FROM tbl ) "
and can also use the XML as now is supported in SQL SERVER
|
|
|
|
|
Using In won't work, you still need to split col2 into it's parts. Doing it with XML or a table var is the same thing, you still need a function to do the splitting of the course column.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yup I agree with you that Split function has to be used but if that data exists in any other table then we can use in
|
|
|
|
|
Hello
I am trying to retrieve all the records form an oracle database table that have a start_date greater than or = Jan 1, 2008, have an end_date greater than May 1, 2009 or the value is null.
There are three columns in the table: start_date, end_date and id.
I need all the id's that have started before April 1, 2008 have an end date that is greater than or = May 1, 2009 or the end_date value is null.
So far I have
select * from table t
WHERE Start_date <= '01-APR-2008'
AND END_DATE >= '01-MAY-2009'
OR END_DATE IS NULL
Any help would be appreciated.
|
|
|
|
|
If your start_date and end_date are defined as type "date", I believe you need to do a TRUNC(start_date) to compare the date. The datatype may actually contain both date and time.
Also, my preference is to use parenthesis around my logical conditions so that I know that they will be evaluated properly.
For example:
WHERE (Start_date <= '01-APR-2008' AND END_DATE >= '01-MAY-2009') OR END_DATE IS NULL
|
|
|
|
|
|
"select max(centreid) from regionmaster where centreid like '" & intBranchPrefix & "%'", DBConnection, adOpenKeyset, adLockOptimistic
If rsMain.RecordCount > 0 Then
intID = Mid(rsMain(0), 3, 13)
End If
intCentreID = intBranchPrefix & (intID + 1)
this is my sql command in vb6 aplication.here i have to create a storeprocedure of this command which create centreid by taking BranchPrefix from application.i have created a storeprocedure but iam getting an error sayingArgument data type decimal is invalid for argument 1 of substring function.in below storeprocedure Note:i have centreid in table in binary(13,2)
create proc GenerateCentreID @intBranchPrefix varchar(20)
as
declare @MaxCentreID bigint,@intID varchar(20)
set @MaxCentreID=(select convert(bigint,max(substring(centreid,3,13)))
from regionmaster
where centreid like @intBranchPrefix+'%'
)
set @intID=@intBranchPrefix + convert(varchar(13),@MaxCentreID+1)
select @intID
go
|
|
|
|
|
I have got a stored procedure which executes very fast in Sql Server Management Studio, but when I call it with the same parameters from C#, it executes slower and sometimes even throws timeout exception.
Does anybody know the reason?
hint: I have noticed that when I comment out one of the conditions in where clause, the query executes very fast both on SSMS and C#.
Another point is that when I remove the comment and put the condition back in the query (which causes the stored procedure to compile again) the query execution becomes a little faster in C#, but after a while it becomes slow again (all with the same parameters)
|
|
|
|
|
I think that for anybody to stand a chance of answering your question, you should edit your post and include the stored procedure and the c# code that calls it.
Please do NOT reply to me, this is just some advice to help you get an answer. 
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
May I suggest ...
In SQL Server Studio Manager, execute your stored procedure, but under the menu "Query" choose the option to "Display Esitmated Execution Plan". This will give you great insight into how the SQL server is going to retrieve your data. You want to avoid large "scans" of tables, try to take advantage of indexes.
Also, you may want to look into SQL Profiler and watch what is going on while your code executes.
|
|
|
|
|
Thanks for your advice,
All the indexes are being used (index seek) and no table scan is happening. That's why the query gets executed very fast in Sql Server. That's why I wonder what the difference is when I execute it from code with the same parameters.
|
|
|
|
|
I would now look into the SQL Profiler, because you can now monitor how your application is interacting with the database. Things to check on the client side would be network traffic, memory usage and CPU usage while this is executing.
Sounds like a fun challenge. 
|
|
|
|
|
I found something recently that discussed arith_abort (I think, the link is at work) which is set on by default, setting it to off apparently made a difference to some procs that showed this behavior.
I have seen this behavior and after doing all the optimising possible the proc was still slow, that's when I started chasing down the more esoteric suggestions.
If I find the link I will post it Monday....
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi there,
how can you call a stored proc with OUT param in MySQL?
CREATE PROCEDURE spListUser (
...
PrimaryEmailFilter nvarchar(255),
..
OUT NumItemsSelected int
)
BEGIN
...
END;
I tried different things none work.
<br />
CALL spListUser (<br />
...<br />
@NumItemsSelected<br />
);<br />
<br />
CALL spListUser (<br />
...<br />
OUT @NumItemsSelected<br />
);<br />
<br />
CALL spListUser (<br />
...<br />
@NumItemsSelected OUT<br />
);<br />
In the end, I figured that it worked with Command line client but not with TOAD. What the hell is this? Anybody come across this before?
Thanks
dev
|
|
|
|
|
How can I go about building a database for say, a toy company? Using the microsoft SQL 2008? I know SQL commands whcih Ihave used to retrieve data using VB script from an already built website.
But I want to build my own DB and then connect it to my .NET framework.
|
|
|
|
|
Well one way to start would be by building ER diagrams.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Yeah Henry, that would be a good start. But then, how do I start up SQL 2008 and how do I get the Microsoft SQL editor? I mean, i see a downloaded link that says SQL 2008 on my desktop after I installed Visual Developer. But, I am unanle to figure out where do i go from there to build my DB??
|
|
|
|
|
Buy a book on SQL, your question is unanswerable in a forum, you are asking someone to teach you how to build a database, this can take years to perfect and certainly weeks/months to get the basics.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the Curt reply Mycroft.
|
|
|
|
|
Hi
The system table sysmergesubscriptions has changed from SQL Server 2000 to SQL Server 2005. The columns distributor and srvid are no more available. I have a stored procedure that used this columns that is not working when it should be used on an SQL Server 2005 or 2008.
Are there any replacements for this columns and is it possible to use them also on SQL Server 2000?
Here is the query that uses this columns:
DECLARE repl_cur CURSOR FOR
SELECT
mp.name,
me.srvname,
mp.publisher,
mp.publisher_db,
ms.db_name,
ms.subscription_type,
ms.distributor
FROM
sysmergepublications mp
INNER JOIN
sysmergesubscriptions ms
ON ms.pubid = mp.pubid
INNER JOIN
master.dbo.sysservers me
ON ms.srvid = me.srvid
INNER JOIN
sysmergearticles ma
ON ma.pubid = ms.pubid
WHERE
ma.name = 'PasswordCounter'
AND ms.subscription_type = 1
Thanks for your help
Greets
Roland
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
