2
Reply

Make SQL querys on view faster on c# and SQL server 2014

Cassie Mod

Cassie Mod

Feb 17 2016 10:00 AM
321
Hi,
 
I have to expand an application that is already made, wich is no problem. However hhe application uses several querys. 4 of that queries on views are very slow. Is there a way to make them faster ??
 
query 1:
command.CommandText = @"select Year,Month,Day,NrOfCalls,Duration,GrossExVat
from vwRatedOutboundCdrsPerDay vw
where vw.Year = @Year
and vw.Month = @Month
and (@Day is null or vw.Day=@Day)";
command.Parameters.Add("@Year",SqlDbType.Int).Value = year;
command.Parameters.Add("@Month",SqlDbType.Int).Value = month;
command.Parameters.Add("@Day", SqlDbType.Int).Value = day.HasValue ? (object)day.Value : DBNull.Value;
// this query takes 1,5 minute 
 
query:2
command.CommandTimeout = 0;
command.CommandText = @"select Year,Month,Day,NrOfCalls,Duration,EuGrossExVat
from vwRatedBillableOutboundCdrsPerDay vw
where vw.Year = @Year
and vw.Month = @Month
and (@Day is null or vw.Day=@Day)";
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
command.Parameters.Add("@Month", SqlDbType.Int).Value = month;
command.Parameters.Add("@Day", SqlDbType.Int).Value = day.HasValue ? (object)day.Value : DBNull.Value;
// takes 1,15 minute 
 
query:3
command.CommandText = @"select NrOfCalls, Duration, EuGrossExVat
from vwRatedBillableOutboundCdrsPerDay vw
where vw.Year = @Year
and vw.Month = @Month
and vw.Day=@Day";
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
command.Parameters.Add("@Month", SqlDbType.Int).Value = month;
command.Parameters.Add("@Day", SqlDbType.Int).Value = day;
// takes about 5 minutes 
 
query: 4
command.CommandText = @"select NrOfCalls, Duration, GrossExVat
from vwRatedOutboundCdrsPerDay vw
where vw.Year = @Year
and vw.Month = @Month
and vw.Day = @Day";
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
command.Parameters.Add("@Month", SqlDbType.Int).Value = month;
command.Parameters.Add("@Day", SqlDbType.Int).Value = day;
// takes about 5 minutes 
 

Answers (2)