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