parameter problem with report

I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name

The data source for this report is an oracle db

SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND

(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")

GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,

sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||

Can you mark the helpful response as an answer?

Parameter Optimization

Hi guys,
I have a rather complex query which accepts 2 date parameters. When the
dates are hard-coded the query runs in 2 seconds; however when a parameter
is passed with the same values the query runs in excess of 2 minutes.
I got around this by building the entire query into a varchar variable, and
then using the exec(strexpression).
Is there a problem in the way the SQL server optimizer handles parameterized
queries? And what are the alternate ways to get around this?
Thanks,
JustinMaybe post some ddl on this specifying what the datatypes are.
"Justin" wrote:

> Hi guys,
>
> I have a rather complex query which accepts 2 date parameters. When the
> dates are hard-coded the query runs in 2 seconds; however when a parameter
> is passed with the same values the query runs in excess of 2 minutes.
>
> I got around this by building the entire query into a varchar variable, an
d
> then using the exec(strexpression).
>
> Is there a problem in the way the SQL server optimizer handles parameteriz
ed
> queries? And what are the alternate ways to get around this?
>
> Thanks,
> Justin
>
>|||I noticed that if you have many records in tables, conversion from varchar
to nvarchar or back can cause behavior you talk about. maybe when you
hardcode dates u use different type than when you pass them as params?
peter

Parameter Layout on SRS report

Hi, I have a list of parameters that I am showing for an srs report
and want them to visually look like this:
Company: <combo box>
Date Range Type: <combo box> From: <combo box> To: <combo
box>
but it's showing them like this:
Company: <combo box> Date Range Type: <combo box>
From: <combo box> To: <combo box>
I don't seem to have a lot of control over this. Is there a way to
set this up so I can format the look of the parameters the way I want
them?You don't have any control over this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doogie" <dnlwhite@.dtgnet.com> wrote in message
news:77bf060f-5f64-4bc2-861a-765da97425c5@.60g2000hsy.googlegroups.com...
> Hi, I have a list of parameters that I am showing for an srs report
> and want them to visually look like this:
> Company: <combo box>
> Date Range Type: <combo box> From: <combo box> To: <combo
> box>
>
> but it's showing them like this:
> Company: <combo box> Date Range Type: <combo box>
> From: <combo box> To: <combo box>
> I don't seem to have a lot of control over this. Is there a way to
> set this up so I can format the look of the parameters the way I want
> them?|||On Mar 6, 11:47=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You don't have any control over this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Doogie" <dnlwh...@.dtgnet.com> wrote in message
> news:77bf060f-5f64-4bc2-861a-765da97425c5@.60g2000hsy.googlegroups.com...
>
> > Hi, I have a list of parameters that I am showing for an srs report
> > and want them to visually look like this:
> > Company: =A0<combo box>
> > Date Range Type: =A0<combo box> =A0 =A0 =A0 From: <combo box> =A0 To: <c=ombo
> > box>
> > but it's showing them like this:
> > Company: =A0<combo box> =A0 =A0 =A0 =A0 =A0Date Range Type: =A0<combo bo=x>
> > From: <combo box> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0To: <combo box>
> > I don't seem to have a lot of control over this. =A0Is there a way to
> > set this up so I can format the look of the parameters the way I want
> > them... Hide quoted text -
> - Show quoted text -
Is there some option for maybe creating hidden combo boxes or
something that may allow me to space things up? I tried to add a
hidden text box and because it was hidden, it didn't actually space
anything out.|||The only way to have control is to have your own website and your own web
page and integrate with RS. A lot of effort though. Truly, there is no
workaround that I am aware of. If there was a workaround I would use it
myself because I do have some reports where this would come in handy. I not
aware of any changes in this area for 2008 either.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doogie" <dnlwhite@.dtgnet.com> wrote in message
news:16b3a5c4-1031-478c-bd47-af607ac3b91b@.q78g2000hsh.googlegroups.com...
On Mar 6, 11:47 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You don't have any control over this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Doogie" <dnlwh...@.dtgnet.com> wrote in message
> news:77bf060f-5f64-4bc2-861a-765da97425c5@.60g2000hsy.googlegroups.com...
>
> > Hi, I have a list of parameters that I am showing for an srs report
> > and want them to visually look like this:
> > Company: <combo box>
> > Date Range Type: <combo box> From: <combo box> To: <combo
> > box>
> > but it's showing them like this:
> > Company: <combo box> Date Range Type: <combo box>
> > From: <combo box> To: <combo box>
> > I don't seem to have a lot of control over this. Is there a way to
> > set this up so I can format the look of the parameters the way I want
> > them... Hide quoted text -
> - Show quoted text -
Is there some option for maybe creating hidden combo boxes or
something that may allow me to space things up? I tried to add a
hidden text box and because it was hidden, it didn't actually space
anything out.

Parameter Either Or

Hi

Background

I have a report that can be either date based (start and end dates, shows multiple jobs) or job number based (shows one job). Parameters all setup and it's working, it uses one SP. I have Allow Null selected for each of the three possible entry boxes. SP says IF JobNumber IS NULL Then Do this ELSE Do that

Problem

It looks messy. I have a label on the start date parameter "EITHER Please select a start date:" And a label on the job number parameter "OR Please select a job number:"

The user can then tick/untick NULL and enter either a date or a job number. Any way of making this work a little better? An initial parameter of report type > Job Number Or Date. Then just show the appropriate parameter for example. To do this I'd need to (I think) dynamically hide a parameter, can this be done? Any other ideas?

Cheers

Just have one parameter (string)

You can always use a code block to check formatting and provide msgboxs.

Then just convert the string to the required datatype|||

You can have three parameters. Use first one to decide whether you would want to run the report with parameter 1 or Parameter 2.

So, the out of three user will pick 2 parameters. and in the back end you will use the first one to decide which one to use and which one to discard.....

|||

Thanks for the replies. Adolf - I have 3 parameters so 1 free entry text box isn't really going to work for me.

TechQuest - I have no problem making it work in the back end, I want the front end to display either parameters start & end dates or parameter job number. If I have the initial parameter with options of Date Or Job Number. They select Date, how do I make just the Date parameters appear?

I may not understand exactly what you mean but you say the user will pick 2 out of the 3 parameters and the back end will sort it, but that's what I have now

|||

Unfortunately there is no way for you to achieve a much better user experience. RS does not allow you to show/hide parameters dynamically at run time.

What I suggest is maybe setting the value of the unused parameter something meaningfull to show that it should not be used. A lot depends on your current setup of the parameters i.e is job id a dropdown or a text box? Is date from and to date picker controls or textboxes?

So, based on the previous suggestions, I would say have the following:

Parameter 1 = Filter Type {Job ID, Date Range}

Parameter 2 = Job ID, I suggest you make this a dropdown of valid job ID's. When parameter 1 = Date Range populate this dropdown with just 1 value that says "N/A - Please select a date range".

Parameters 3 & 4 = You have a couple options here, if you make them date pickers then you can't set their values to to infomative text. You could if you make them text boxes but then you wouldn't get the nice functionality of a date picker.

Either way it's not going to be as nice as coding your own UI.

|||

Thanks for that Adam. At least I can stop searching for an 'nice' answer now as I know it's not possible. I'll go down the route you suggest.

Cheers

sql

Parameter Either Or

Hi

Background

I have a report that can be either date based (start and end dates, shows multiple jobs) or job number based (shows one job). Parameters all setup and it's working, it uses one SP. I have Allow Null selected for each of the three possible entry boxes. SP says IF JobNumber IS NULL Then Do this ELSE Do that

Problem

It looks messy. I have a label on the start date parameter "EITHER Please select a start date:" And a label on the job number parameter "OR Please select a job number:"

The user can then tick/untick NULL and enter either a date or a job number. Any way of making this work a little better? An initial parameter of report type > Job Number Or Date. Then just show the appropriate parameter for example. To do this I'd need to (I think) dynamically hide a parameter, can this be done? Any other ideas?

Cheers

Just have one parameter (string)

You can always use a code block to check formatting and provide msgboxs.

Then just convert the string to the required datatype|||

You can have three parameters. Use first one to decide whether you would want to run the report with parameter 1 or Parameter 2.

So, the out of three user will pick 2 parameters. and in the back end you will use the first one to decide which one to use and which one to discard.....

|||

Thanks for the replies. Adolf - I have 3 parameters so 1 free entry text box isn't really going to work for me.

TechQuest - I have no problem making it work in the back end, I want the front end to display either parameters start & end dates or parameter job number. If I have the initial parameter with options of Date Or Job Number. They select Date, how do I make just the Date parameters appear?

I may not understand exactly what you mean but you say the user will pick 2 out of the 3 parameters and the back end will sort it, but that's what I have now

|||

Unfortunately there is no way for you to achieve a much better user experience. RS does not allow you to show/hide parameters dynamically at run time.

What I suggest is maybe setting the value of the unused parameter something meaningfull to show that it should not be used. A lot depends on your current setup of the parameters i.e is job id a dropdown or a text box? Is date from and to date picker controls or textboxes?

So, based on the previous suggestions, I would say have the following:

Parameter 1 = Filter Type {Job ID, Date Range}

Parameter 2 = Job ID, I suggest you make this a dropdown of valid job ID's. When parameter 1 = Date Range populate this dropdown with just 1 value that says "N/A - Please select a date range".

Parameters 3 & 4 = You have a couple options here, if you make them date pickers then you can't set their values to to infomative text. You could if you make them text boxes but then you wouldn't get the nice functionality of a date picker.

Either way it's not going to be as nice as coding your own UI.

|||

Thanks for that Adam. At least I can stop searching for an 'nice' answer now as I know it's not possible. I'll go down the route you suggest.

Cheers

Parameter Date Formatting

Hey,
I've got a problem - got Start and End date parameters in my report and
on my dev box they show up in Australian format as it should be but on
the production server it's in American format! What do I do? I don't
really want to try setting dates as strings... Installed SP2, it had no
effect...Check the server. It sounds like it's culture settings are not Australian.
-Tim
"masterslave" <ocbka1@.gmail.com> wrote in message
news:1148967379.266799.284360@.u72g2000cwu.googlegroups.com...
> Hey,
> I've got a problem - got Start and End date parameters in my report and
> on my dev box they show up in Australian format as it should be but on
> the production server it's in American format! What do I do? I don't
> really want to try setting dates as strings... Installed SP2, it had no
> effect...
>|||Thanks for your reply Tim, I've checked the server and all settings are
correct! It seems that the date format is actually set on the client
machine in IE language settings... Is that possible? If so, this is
really bizarre...

Parameter Date Format!

Hi,

How to control the Datetime Parameter's format, such as I need the user input Date and Time, but default, the user can only select date, but can not input the time.

How to solve it?

Thank you.

Jeffers,

You could give your users two textbox parameter for date and time entry, combine to 2 strings ( "mm/dd/yyyy" + "00:00:00" ) and use a cdate function convert to the actual datetime field.

Ham

parameter date format - change to dd/mm/yyyy

Hi All,

I'm using report with a date parameter (user enters a date) and all transactions before that date are displayed. I don't know how to set parameter date format on the displayed report to dd/mm/yyyy.

If it's not a parameter I usually use sql " convert(varchar(10),datefield,103)" but don't know how to use this with parameter.

Thanks

Sonny

Pretty much the same whether for a parameter or for a field expression:

Code Snippet

=Format(Fields!last_edit.Value,"d/M/yyyy")

=Format(Parameters!RPDate.value,"d/M/yyyy")

that should do it.... watch the case-sensitivity; a lower case "m" gives you minutes, not months...

One thing, though: if the user enters the date param directly, you might want to do a cast, something like this just in case there is a data type issue somewhere (of course you might have to do this for whatever code actually *uses* the param as well, I don't know, so maybe it's a dumb thought)

Code Snippet

=Format(CDATE(Parameters!RPDate.value),"d/M/yyyy")

>L<

|||

Hi Lisa,

thanks for the answer. this works with date picker but if I enter the date manually it does not. maybe i have to convert date as you said.

basicall current report paramater is such that you must enter date as YYYYMMDD format. I guess this is default SQL server date format.

I'm on RS patch 0 , never been able to use date picker.

Sonny

|||

WHen you enter the date manually, what does it look like (I'm not asking what the result of the expression is -- I'm asking what you actually enter)?

It's not so much that it has to match the "default sql server date format" as it has to match *some* default, which could be the locale on the server, I'm really not sure.

It is not a big deal to write some code to make sure that your manually-entered date is properly parsed into the format that you want. HOWEVER the reason I just suggested the cast, rather than parsing code, is that you run into the problem of other people maybe entering the manually-entered date differently than you do and possibly parsing out for many different cases.

Since, as you say, the server does have an internal idea of what a date should look like, I find it's often best to write something that does a cast and lets the server figure it out if it can. The worst that will happen is that you'll see #ERROR instead of the expected result, but often SQL Server is pretty smart about figuring out dates, and some of the .NET classes at work in RS (which probably aren't even in SQL Server at the point they are doing this work) may be smart about it also.

If the cast doesn't work, we will proceed further <s>.

>L<

Parameter calendar control : week start date

With Reporting Services (SQL Server 2005), if you include a parameter with a
data type of DateTime, you get a calendar control for the parameter on your
report.
How do you change the first day of the week from Sunday to Monday?
Thanks,
CraigIs your question related to calender control, because you have asked
seperately,
in fact you cant change the calender control. Ofcourse in sql server yes you
can by giving
SET DATEFIRST 1 (Monday)
SET DATEFIRST 7 (Sunday - Default)
Amarnath
"CraigHB" wrote:
> With Reporting Services (SQL Server 2005), if you include a parameter with a
> data type of DateTime, you get a calendar control for the parameter on your
> report.
> How do you change the first day of the week from Sunday to Monday?
> Thanks,
> Craig

Parameter boxes

hey there

Is there a way to reduce the parameter box size.

I am using a date/time picker so want that to be the prominent part for the user?

at the moment it shows like this

StartDate (inputbox) date/time picker

if that inputbox could either be greyed out or smaller?

cheers

jewel

You cannot make changes/customize the standard report toolbar.|||oh ok thanks

Parallelperiod problem

Hi,

I tried to get last 52 weeks from the current period for LY sales.

We have fiscal date hierarchy (year - quarter - month -week) .

In scripts,

Paralleldate ( [date].[hierarchy].[year number], 1 , [date].[hierarchy].currentmember )

this works fine.

but

Scope([Date].[FISCAL YEAR NUMBER].members, [Date].[FISCAL MONTH NAME].members ,[Date].[FISCAL WEEK HISTORICAL NUMBER].members, [Date].[DATE KEY].members);

-- PRIOR YEAR CALCULATIONS

([Time Calculations].[Prior Year]=

(ParallelPeriod([Date].[Hierarchy].[Date].[Hierarchy].[FISCAL WEEK HISTORICAL NUMBER],52,

[Date].[Hierarchy].CurrentMember)

,[Time Calculations].&[ Current Period])

);

End Scope;

) ,

then it doesn't work. I don't know why. ( I can deploy the cube but when I browse the cube, it gives an error without any detailed info. )

FYI, we have fiscal week in historical number ( 52 weeks in fiscal yr) in date table.

then it doesn't work. I don't know why.

Please give me any comments.

A couple of clarifications:

- What is the purpose of this scope statement:

Scope([Date].[FISCAL YEAR NUMBER].members, [Date].[FISCAL MONTH NAME].members ,[Date].[FISCAL WEEK HISTORICAL NUMBER].members, [Date].[DATE KEY].members);

- Why is this assignment enclosed in parentheses:

([Time Calculations].[Prior Year]=

(ParallelPeriod([Date].[Hierarchy].[Date].[Hierarchy].[FISCAL WEEK HISTORICAL NUMBER],52,

[Date].[Hierarchy].CurrentMember)

,[Time Calculations].&[ Current Period])

);

|||

Sorry for the late response.

Now, we've just found some problems with perivios one, Due to the Hierarhcy attribute ( fiscal week key) we can't get last 52 weeks in time calculation called shell dimension.

But,

If I use time intelligence calculation wizard and change some for the prior year calcuation then the formula works fine only in week level .(please refer script as below)

I can't see values in year , quarter, and month levels ( these show 0 value), and in day level I can see the values but values are wrong.

To solve this problem and to view prior year value in all levels( y- q- m -w- day) , how can I approach ?

It seems this formula only works for week level values.

Thank you in advance and please give me any comments.

--Scripts for prior year--

(

[Date].[Fiscal Hierarchy Date Calculations].[Prior Year],

[Date].[FISCAL WEEK HISTORICAL NUMBER].[FISCAL WEEK HISTORICAL NUMBER].Members,

[Date].[Tbl DIM Date].Members

) =

Aggregate(

{ [Date].[Fiscal Hierarchy Date Calculations].DefaultMember } *

{

ParallelPeriod(

[Date].[Fiscal Hierarchy].[FISCAL WEEK],

52,

[Date].[Fiscal Hierarchy].CurrentMember

) : [Date].[Fiscal Hierarchy].CurrentMember

})

- Aggregate(

{ [Date].[Fiscal Hierarchy Date Calculations].DefaultMember } *

{

ParallelPeriod(

[Date].[Fiscal Hierarchy].[FISCAL WEEK],

51,

[Date].[Fiscal Hierarchy].CurrentMember

) : [Date].[Fiscal Hierarchy].CurrentMember

}

);

sql

Paging query without using stored procedure

Hello, my table is clustered according to the date. Has anyone found an efficient way to page through 16 million rows of data? The query that I have takes waaaay too long. It is really fast when I page through information at the beginning but when someone tries to access the 9,000th page sometimes it has a timeout error. I am using sql server 2005 let me know if you have any ideas. Thanks

I am also thinking about switch datavase software to something that can handle that many rows. Let me know if you have a suggestion on a particular software that can handle paging through 16 million rows of data.

Hi~

You may take a look at this:

Efficiently Paging Through Large Amounts of Data (VB)

Efficiently Paging Through Large Amounts of Data(C#)

Hope this helps.

pagenumber in body

Headers are more sophistacated these days. They require client information,
report execution date, page number, report spacific details like account
numbers.
It seems that I cannot put a pagenumber in the body, and I cannot have
dataset info in the header. If I wanted to do this ... could I ? I would
like a 'Power Header' with a mix of global variables like pagenumber mixed
with dataset fields... Can this be done?You this special case you could refer the dataSource on some reportItems
rather than the fields collection.
HTH; Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"d pak" <dipakb@.exchnage.ml.com> schrieb im Newsbeitrag
news:B80D6CE0-CCA3-481C-B1CA-580642863773@.microsoft.com...
> Headers are more sophistacated these days. They require client
> information,
> report execution date, page number, report spacific details like account
> numbers.
> It seems that I cannot put a pagenumber in the body, and I cannot have
> dataset info in the header. If I wanted to do this ... could I ? I would
> like a 'Power Header' with a mix of global variables like pagenumber mixed
> with dataset fields... Can this be done?

page splits/ fragmentation

Hi,
I am inserting data into a table in what I believe is the correct way. The
table is clustered on the date and I am inserting data that 90% of the time
comes in the correct order, ie today I am inserting 2006/04/27 but 10% may b
e
from 2005 or 2004. I think I need a clustered index on the date cause I am
comparing series of dates together.
From the below...
DBCC SHOWCONTIG scanning 'staging_daily_edf_history' table...
Table: 'staging_daily_edf_history' (199671759); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 96488
- Extents Scanned.......................: 12157
- Extent Switches.......................: 12707
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 94.91% [12061:12708]
- Logical Scan Fragmentation ..............: 1.87%
- Extent Scan Fragmentation ...............: 1.49%
- Avg. Bytes Free per Page................: 134.5
- Avg. Page Density (full)................: 98.34%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I believe that my table is not really fragmented. Does anyone else think the
opposite. It worries me though that the insertion become slower and slower a
s
the table icreases in size maybe because of page splits. Any ideas what's th
e
best strategy? should I have my table non clustered instead? Thanks.
Panos.The results show that it is not very fragmented at all and I doubt that page
splits on the clustered index are the issue here. You can also try setting
your fill factor to 90% to remove most of any page splits you may get. How
many non-clustered indexes on this table do you have? Can you give more
details on how you are inserting and how you determine they get slower?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:18D591DA-BCA2-44D8-984A-DE89A4CD5374@.microsoft.com...
> Hi,
> I am inserting data into a table in what I believe is the correct way. The
> table is clustered on the date and I am inserting data that 90% of the
> time
> comes in the correct order, ie today I am inserting 2006/04/27 but 10% may
> be
> from 2005 or 2004. I think I need a clustered index on the date cause I am
> comparing series of dates together.
> From the below...
> DBCC SHOWCONTIG scanning 'staging_daily_edf_history' table...
> Table: 'staging_daily_edf_history' (199671759); index ID: 1, database ID:
> 5
> TABLE level scan performed.
> - Pages Scanned........................: 96488
> - Extents Scanned.......................: 12157
> - Extent Switches.......................: 12707
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 94.91% [12061:12708]
> - Logical Scan Fragmentation ..............: 1.87%
> - Extent Scan Fragmentation ...............: 1.49%
> - Avg. Bytes Free per Page................: 134.5
> - Avg. Page Density (full)................: 98.34%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I believe that my table is not really fragmented. Does anyone else think
> the
> opposite. It worries me though that the insertion become slower and slower
> as
> the table icreases in size maybe because of page splits. Any ideas what's
> the
> best strategy? should I have my table non clustered instead? Thanks.
> Panos.|||Hi,
Well there are 2 more indexes. I guess that must be the problem updating the
non clustered indexes. The way I can see it's slow is because I have a recor
d
on the time the file was processed (insertion via iiss) and can see that it
just takes longer and longer. I guess this is what I should expect when I
have 8m rows and 3 indexes.
Cheers,
Panos.
"Andrew J. Kelly" wrote:

> The results show that it is not very fragmented at all and I doubt that pa
ge
> splits on the clustered index are the issue here. You can also try settin
g
> your fill factor to 90% to remove most of any page splits you may get. Ho
w
> many non-clustered indexes on this table do you have? Can you give more
> details on how you are inserting and how you determine they get slower?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:18D591DA-BCA2-44D8-984A-DE89A4CD5374@.microsoft.com...
>
>|||8M rows and 3 indexes should not slow down the inserts to a degree that
should be easily detectable. Maybe it is the way in which you are doing the
inserts. Can you post the actual code used for the inserts?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:1BA642CC-5545-4B7F-B44A-056CEEB17004@.microsoft.com...
> Hi,
> Well there are 2 more indexes. I guess that must be the problem updating
> the
> non clustered indexes. The way I can see it's slow is because I have a
> record
> on the time the file was processed (insertion via iiss) and can see that
> it
> just takes longer and longer. I guess this is what I should expect when I
> have 8m rows and 3 indexes.
> Cheers,
> Panos.
>
> "Andrew J. Kelly" wrote:
>|||it is a straight forward insert into table, no conditions or anything, can't
be simpler. It doesn't slow down too much but you can still notice. Well the
actual stats is avg insertion 10secs for the whole process at the beginning
and then it goes to avg 26 secs in the end.
Thanks.
"Andrew J. Kelly" wrote:

> 8M rows and 3 indexes should not slow down the inserts to a degree that
> should be easily detectable. Maybe it is the way in which you are doing th
e
> inserts. Can you post the actual code used for the inserts?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:1BA642CC-5545-4B7F-B44A-056CEEB17004@.microsoft.com...
>
>|||10 Seconds for an insert? So this isn't a simple insert then, there must be
more to it. Something you might not think is important may in fact be so
the more details you provide the better chance you will get a proper answer.
Are you sure there are no triggers on the table?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:4F99553F-EEEA-4D19-88E0-CC34B121176B@.microsoft.com...
> it is a straight forward insert into table, no conditions or anything,
> can't
> be simpler. It doesn't slow down too much but you can still notice. Well
> the
> actual stats is avg insertion 10secs for the whole process at the
> beginning
> and then it goes to avg 26 secs in the end.
> Thanks.
> "Andrew J. Kelly" wrote:
>|||I am talking about 20-30,000 rows insertions. Although now it seems to take
longer, more than 2 min. I think it's probably fine. There are no triggers b
y
the way on the table. You think that few odd rows which don't come in order
will not cause a massive problem. Ill also change the fill factor and see if
that's better. Appreciate your help.
Panos.
"Andrew J. Kelly" wrote:

> 10 Seconds for an insert? So this isn't a simple insert then, there must
be
> more to it. Something you might not think is important may in fact be so
> the more details you provide the better chance you will get a proper answe
r.
> Are you sure there are no triggers on the table?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:4F99553F-EEEA-4D19-88E0-CC34B121176B@.microsoft.com...
>
>|||You still are not providing any real details as to exactly how you do these
inserts and that makes it very hard to give any solid advice. In my opinion
that is still too long for just 20 or 30K rows. Have you checked for
blocking? What about your disk queues? Is your log file on a separate Raid
1 from any of the data files?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:CE4272A5-1F5B-4CB4-89A0-38CA5BDCC3F0@.microsoft.com...
>I am talking about 20-30,000 rows insertions. Although now it seems to take
> longer, more than 2 min. I think it's probably fine. There are no triggers
> by
> the way on the table. You think that few odd rows which don't come in
> order
> will not cause a massive problem. Ill also change the fill factor and see
> if
> that's better. Appreciate your help.
> Panos.
> "Andrew J. Kelly" wrote:
>