|
The best way to know is to try it, make the tables, fill them with data, execute queries showing the execution plan. In the execution plan, SQL Server will tell you what indexes were used.
In general, If you have composite keys,indexes the 1st column in the index must be referenced in the query (specifically in the where or join) for sql server to consider using this index.
about you samples:
based on the above mentioned information and some experience I have with this, these can be the expected behavior of SQL Server:
where field1 = a (Index Will be used)
where field3 = a (Index Will not be used)
where field1 = a and field2 = b (Index Will be used)
where field2 = a and field3 = b (Index Will not be used)
where field1 = a and field3 = b (Index Will be used)
where field1 = a and field4 = b (Index Will be used)
where field4 = a and field1 = b (Index Will be used, sql server is smart enough  )
After adding index on Field4:
where field1 = a and field4 = b (one of the two indexes will be used, not both)
where field4 = a and field1 = b (one of the two indexes will be used, not both)
where field3 = a and field4 = b (index on field4 will be used)
note that whether SQL Server will use the index or not depends on many factors, for example statistics and granularity on index data.
|
|
|
|
|
Hi,
I have one method in the Data Access Layer like the below:
LoadPersonsData()
{
IDataReader reader= db.ExecuteReader("Procedure name");
Now here I need to call another private metho which will load part of data.
PersonData d = new PersonData();
While(reader.read())
{
D.name =reader["fname"];
…
D.Subdata = LoadSubdata(d, reader);
D.DOB = reader["dob"];
}
D.Subdata = LoadSubdata(d, reader); -- This line is also not getting the result
D.DOB = reader["dob"]; -> The problem here is after loadSubData is called next I am trying to get the next field from reader, Here I am getting
No data exist for the row or column.
How to pass the datareader to LoadSubdata method. I just created the sub method to reduce the complexity complained by the DevPartner tool.
Thanks
|
|
|
|
|
The DataReader uses the connection exclusively and this may be your problem.
|
|
|
|
|
In Reader.read() u r selecting a specific row and then u r sending whole reader it wont work, I think u should do it same function instead of calling another one...
|
|
|
|
|
Prateek G wrote: then u r sending whole reader it wont work
Why shouldn't it work?
Prateek G wrote: I think u should do it same function instead of calling another one...
Uugh! That way misery lies.
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
In case I am reading Nth row reader must be having all the rows, How he will be able to select that particular row at that time.....
|
|
|
|
|
Prateek G wrote: In case I am reading Nth row reader must be having all the rows
The reader disgorges one row at a time. If you pass a reader to a method it will be on what ever row that it was at when it was passed. The reader doesn't reset when it is passed around.
Prateek G wrote: How he will be able to select that particular row at that time.....
He most likely doesn't need to. He will be on the row he wants to read.
You are making an awful lot of assumptions about what the OP is doing. The code he supplied is not valid and his description has many holes.
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
Well, there may be something that is obviously wrong if you posted the ACTUAL code. The code you have posted contains several inconsistencies. You create a variable d and then appear to refer to it as D later on - these are separate variables and the code will not compile.
Also, the problem is in LoadSubData, but you have not shown us what LoadSubData does.
Please post the ACTUAL code if you want a chance to get a reasonable answer rather than blind guesses.
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
Can u paste the code of LoadSubdata(d, reader) function..
Are u using While(Reader.read()) method inside that function..
|
|
|
|
|
If you have a column of numeric data, then have a cell with the value result of #VALUE! and then more cells in the column with the result of numbers, how do you add the numbers together without the total result having the #VALUE! result? I tried it with the AUTOSUM feature, but that function only reads the cells from the bottom up to the #VALUE! result. I tried extending the cell range for the AUTOSUM feature, but #VALUE! is still the total result. I changed the #VALUE! field to a text amount with the AUTOSUM extended to the top of the numbers in the column, and it ignores the alphanumeric field. It only does not work when one of the fields has the #VALUE! result. What is the solution?
|
|
|
|
|
Hi, I need a little help with "" signs in my code i know the + need to be treated specially but i am newbie.
sSQL += "UPDATE Queue SET Active='False' WHERE Unit = ' + dlUnit.Value' AND Status = 'Queued'";
thanks
|
|
|
|
|
s3rro wrote: Hi, I need a little help with "" signs in my code i know the + need to be treated specially but i am newbie
Use parameters rather than attempt to inject values into a SQL string. If you inject stuff then you are setting yourself up for a SQL Injection Attack.
To pre-empt the question: Some people at this point like to tell me that they are just learning and are not concerned about that at the moment. My response to that is that if you never learn how to do it wrong you will always do it right.
Please read SQL Injection Attacks and Tips on How to Prevent Them[^]
By following the advice in the above article you will also solve your current problem.
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
Please feel free to jump in on this one[^]. I'm losing the will to live.
|
|
|
|
|
I've been working on a query today which I've completed and can make run in about 7 seconds. However, when I create a stored procedure using the exact query code, the execution time increases to 103 seconds!
I'm sure there's a server setting or something like that that I've missed but it's been a long day already and I'm totally out of ideas!! Does anyone have any suggestions for things i can look at so I can go home?! 
Thanks in advance
It definitely isn't definatley
|
|
|
|
|
Run the SQL profiler when your procedure executes to see what it's doing. Then take the query(ies) that are identified in the profiler and run them manually - get the estimated execution plan to see what's happening.
|
|
|
|
|
Peter,
I've actually tried doing this (by including the actual excecution plan when running) but this still doesn't seem to elude any differences; each of the queries (20 in each) has approximately the same batch cost and the CPU costs are the same as well.
It's almost as if this query behaves differently when it's a stored procedure since that's the only variable I've changed. It's a SQL2000 server if that makes any difference and the query uses a few of temporary tables to do its calculations.
Any other ideas??
Cheers
Dave
It definitely isn't definatley
|
|
|
|
|
Just as a quick thought - you haven't got with recompile set on the stored procedure have you?
|
|
|
|
|
That is really odd. Have you tested a few times alternating between the sproc and the adhoc?
Both should pick up the same cached execution plan - and be ballpark the same speed (sproc should be slightly longer, but not noticibly). Of course the first one you run will have all the overhead of figuring out the execution plan and pulling the table indexes off disk...
|
|
|
|
|
Can you upload the stored procedure so we can take a look?
|
|
|
|
|
I don't think my client will allow that unfortunately - confidentiality and all...
It definitely isn't definatley
|
|
|
|
|
I m facing problem with sql procedure. i want to retrieve records those opening balance is < fromdate if a client open new account between my selected date range so open balance should be 0.00. becoz i m taking open balance < from date.
please help ..........
my store procedure ...
CREATE PROCEDURE LedgerStatement_RPT(
@FROMBOID CHAR(16),
@TOBOID CHAR(16),
@FROMDATE VARCHAR(10),
@TODATE VARCHAR(10),
@GROUPCODE VARCHAR(6)
)
AS
SET NOCOUNT ON
--EXEC LedgerStatement_RPT '1201980000003843','1201980000003843','08/01/2006','01/03/2007',NULL
--set @FROMBOID='1201980000003843'
--set @TOBOID='A001'
--set @FROMDATE = '08/01/2006'
--set @TODATE ='01/03/2007'
Declare @OpeningBalance decimal(15,3)
Declare @ClosingBalance decimal(15,3)
set @ClosingBalance =
(select IsNull(sum(Cr),0)
from Voucher
WHERE BOID between ISNULL(@FROMBOID,BOID) AND ISNULL(@TOBOID,BOID)
and CreatedOn Between ISNULL(@FROMDATE,CreatedOn) AND ISNULL(@TODATE,CreatedOn)) - (select IsNull(sum(Dr),0)
from Voucher
WHERE BOID between ISNULL(@FROMBOID,BOID) AND ISNULL(@TOBOID,BOID)
and CreatedOn Between ISNULL(@FROMDATE,CreatedOn) AND ISNULL(@TODATE,CreatedOn))
SELECT ISNULL(V.BOID,'') BOID,CM.FirstHolderName,IsNull(ISNULL(V.YearCode,'') +'/'+ cast(V.VNo as varchar),'') as BillNo,
IsNull((select BillDate from BillHeader
where BillHeader.DueDate= V.DueDate),V.CreatedOn) as BillDate,
IsNull(V.Narration,'') As Remarks,Dr,Cr,CM.BOGroupCode,ISNULL(X.OpeningBalance,0.00) AS OpeningBalance,ISNULL(Y.ClosingBalance,0.00) AS ClosingBalance
From Voucher V,ClientMain CM,
(select V.BOID,IsNull((sum(V.Cr)-sum(V.Dr)),0.00) as OpeningBalance
from Voucher V,ClientMain C
WHERE V.BOID between ISNULL(@FROMBOID,V.BOID) AND ISNULL(@TOBOID,V.BOID)
and C.BOID = V.BOID
and C.BOStatus = 1
and V.CreatedOn < ISNULL(CONVERT(DATETIME,@FROMDATE) + '23:59:59.998' ,V.CreatedOn)
group by V.BOID)X,
(select V.BOID,IsNull((sum(V.Cr)-sum(V.Dr)),0.00) as ClosingBalance
from Voucher V,ClientMain C
WHERE V.BOID between ISNULL(@FROMBOID,V.BOID) AND ISNULL(@TOBOID,V.BOID)
and C.BOID = V.BOID
and C.BOStatus = 1
and V.CreatedOn <= ISNULL(CONVERT(DATETIME,@TODATE) + '23:59:59.998',CreatedOn)
--and V.CreatedOn <= ISNULL(@TODATE,V.CreatedOn)
group by V.BOID)Y
where V.BOID=CM.BOID
AND V.BOID between ISNULL(@FROMBOID,V.BOID) AND ISNULL(@TOBOID,V.BOID)
AND V.CreatedOn Between ISNULL(CONVERT(DATETIME,@FROMDATE),V.CreatedOn) AND ISNULL(CONVERT(DATETIME,@TODATE) + '23:59:59.998',CreatedOn)
-- AND V.CreatedOn < ISNULL(@FROMDATE,V.CreatedOn)
-- AND V.CreatedOn > ISNULL(@TODATE,V.CreatedOn)
AND ISNULL(CM.BOGroupCode,-1) = ISNULL(@GROUPCODE,ISNULL(CM.BOGroupCode,-1))
AND V.BOID=X.BOID
AND V.BOID = Y.BOID
order by billdate
I am software programming engineering student
and i want to develop in this field so Please help me whenever I want ur support.
Thanks & Regards
Mohammad Faiz Siddiqui
|
|
|
|
|
mohd faiz wrote: @FROMDATE VARCHAR(10),
@TODATE VARCHAR(10),
Why are you not holding these as DATETIME or SMALLDATETIME?
mohd faiz wrote: so Please help me whenever I want ur support.
What about when we are able to give it. If you want help whenever YOU want it then you should look at paying for a support contract!
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
Colin Angus Mackay wrote: If you want help whenever YOU want it then you should look at paying for a support contract!
Sounds good. I'll go and put a draft contract together then 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hi friends,
Can any one help me in, How can i read the data from MSSQl server (Let us say at x database and all tables in that database) and then convert data in to a text format and so that i can save and upload that data into [b]MySql [/b]Data base.
I was following a procedure which is satisfactory for few tables but as the table number and size grows my procedure will become a tedious one.
Anyhow i will tell the procedure i am following
I created a DataIntegrationServices Project which is available in MSSQL2005 server Business Intelligence Development Studio.
There i created a dataflowtask where we can give the origin and destination files. Origin was a OLEDB connection to connect the database and destination was a FlatFile. In this way i can convert data from one table to the text format with delimiters we select.
But i need to convert some hundred tables, can any one help me in this regard.
I use C# language for developing my applications.
Can any one suggest me an alternative or enhance the same procedure by certain modifications.
Please don't suggest third party tools which i have to buy, i prefer working with code or free available tools.
Thank you in advance.
N.Raghavendran.
Raghavendran
|
|
|
|
|
You could use SQL Server Integration Services (SSIS) to achieve this. For each table, set up a task that writes the contents of that table out to an individual flat (text) file.
This site[^] has plenty of useful resources for SSIS.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
