Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
Hi all,
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
I probably posted my question in the wrong forum so please check here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3009605&SiteID=17
thanks
I get the following error when I try to load the mining model in the mining model viewer
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[NeuralNet].[GetAttributeValues]' function does not exist.
I get a similar error when I try to load the Load Mining Accuracy Chart
Failed to execute the query due to the following error:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[AllOther].[GenerateLiftTableUsingDatasource]' function does not exist.
I have OWC 11 installed. What am I missing here?
Thanks
i'm building a time series model in visual studio, forecasting sales. but it shows
The graph cannot be created because of the following error:
'All actual columns are ending in NULL: data unacceptable for viewer'. (Microsoft Visual Studio)
can anyone help solve this? the data is by month (for 3 years) and i want to forecast 3 months in the future.
thanks!
lxm
Hi, all here,
I want to hear from you for your experiences on how can we be convinced by what the model predicts particularly for Time Series algorithm? As we are not able to see the model accuracy chart for that, in this case, how can use the model results? E.g. we wanna know the possible sales amount of next month for a particular store in order to buy in the goods, in this case, how can we make the most of the prediction by the model? To shop ower, well, if the result is too far away from the usual sales amount, then it is unbelievable, thus, in this case, what else can we try? Keep training the models until its results sounds reasonable? Or what else can we try?Thanks in advance for your advices and help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi,
I have built a Time Series model to forecast Sales Value. I have used data form Jan 2005 to Sep2006 to train the model.
When
I write a DMX query to forecast the sales value for next 5 months, it is throwing me the output in the following format.
$TIME Sales Value
200610 2874
200611 2875
200612 2874
200613 2874
200614 2874
whereas the output i expected was.
$TIME Sales Value
200610 2874
200611 2875
200612 2874
200701 2874
200702 2874
What changes should i make in the model to get the output in the above format.?
Hi,
I have built a Sales Forecast model to predict the sales
value.
Along with making historic predictions for previous time
periods I also want to retrieve the actual sales values for those periods.
How can I achieve this in a time series model?
I also would like to know how do mining models store the data.
Do they store the data in the same table/view format
as their respecive data source view or in the Model Content format.
As the Microsoft Time Series algorithm implementation is based upon the Autoregressive Tree approach described in:
C. Meek, D. M. Chickering, D. Heckerman. Autoregressive Tree Models for Time-Series Analysis. In Proc. 2nd Intl. SIAM Conf. on Data Mining, 2002 (SDM-02). SIAM, pp. 229 €“ 244. http://www.siam.org/meetings/sdm02/proceedings/sdm02-14.pdf.
The model estimated is refererred to as an instance of "... autoregressive tree models of length p, denoted ART(p). An ART(p) model is an ART model in which each leaf node of the decision tree contains an AR(p) model, and the split variables for the decision tree are chosen from among the previous p variables in the time series..." (see the last paragraph of p. 2 of the paper).
What is the value of "p" used in the Microsoft Time Series implementation -- specifically, how many previous time series variables are used in estimating the model? It doesn't appear that this value can be specified in the algorithm parameters -- is that correct?
Thanks,
- Paul
i want create a model to predict what product i should Ads for customer at time.
example: in summer, i should show the , drinks (coca, pepsi) , ice food, bikini, sandals, glasses. in winter, i should show shoes, coats, hot food....
i have some table:
order(orderid, time,cusid...)
product(productid,..)
orderdetails(orderid, productid..)
customers(cuisid,....)
I broke up my cube into 24 partitions. There are about 630M total fact rows in that cube.
When I open the cube to browse in BIDS or SQL Management Studio it takes very long time to open (I think 30 minutes).
Profiler does not show that it's running a query, but messages like this keep appearing throughout the time it's opening to browse:
Progress Report Begin, 14- Query, Started reading data from the 'p0' partition.
Progress Report End, 14- Query, Finished reading data from the 'p0' partition.
Progress Report Begin, 14- Query, Started reading data from the 'p10' partition.
Progress Report End, 14- Query, Finished reading data from the 'p10' partition.
and goes on like that....
Hi, all,
Again I encountered a very strange problem which displayed the predicted attribute values as percentage format? The data type of the attribute is actually double, why is that?
That's really frustrated.
Thanks a lot in advance for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
I am developing a multiple regression model for a super market site selection with 11 predictors.
As soon as I press the model viewer tab, the following message appears:
The server content appears to be out of date.
Would you like to build and deploy the project first?
I opt no and the error message pops up:
The tree graph cannot be created because of the following error:
'Either the user, ......., does not have access to the ....... database, or the database does not exist.'.
Could anyone mine out the cause please....
saji
Is there anyway to print the mining model diagrams (dependency network, decision trees etc)
Thanks,
Carrie
In order to setup my forecasting mining model I have created a special view that runs against my fact table and creates time series on the level I need.
Code Snippet
Select DFUKEY, DATE, QTY from Dim_FACT where DFKEY like '020%'
So I get the following input fr my model:
time series key (e.g. DFUKEY)
date (time key)
QTY (to be predicted)
For testing purposes I created a small view (similar to AdventureWorks) that only contained a few time series. The model was created and processed in ~2 minutes or less. The viewer came up almost immediately and I was able to see results.
Now my real view has about 25000 time series I need a forecast for and that I also like to review in the viewer. If I create a mining model against that bigger view the processing takes ~15m or so and the viewer is likely to time out.
The worst part thought is when I try to get the forecast for a time series (see query below) it takes minutes before the answers come back.
Code Snippet
SELECT FLATTENED [DFUKEY], PredictTimeSeries([QTY], 12)
FROM [TimeSeries]
WHERE [DFUKEY] = '02078147AG'
I would understand if without the WHERE clause it would take long but as is?
I am pretty sure I created the model the right way so I wonder if somebody can give me recommendations as to how forecast larger numbers.
Finally I intend to call the foarcast for a particular series using an Action from within a cube.
Thanks in advance.
Dirk
Hi, all here,
I tried to find the graphs I saved from Data Mining Model Viewer, but where is it saved? As we see from the mining model viewer we could save the graphs there by clicking the 'save graph' button, but where is the graph?
Really need help for that.
Thank you very much in advance for any guidance and help for that.
With best regards,
Yours sincerely,
I've successfully created and processed a very simple neural network mining model (defined against a cube). However, when I go to the model viewer in BI studio, it displays the following error:
"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format.."
Any ideas about what's going wrong? This is with SQL Server 2005 SP1.
We've successfully processed a large decision tree model in SQL Server 2005. When I try to view the tree in the mining model viewer, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
The tree graph cannot be created because of the following error:
'Exception of type 'System.OutOfMemoryException' was thrown.'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476
The link provides no other documentaiton on the error.
We're using 64-bit SQL on a Dell Workstation running XP-64 with 16GB of memory. From my view of things we aren't close to running out of memory. Since the model processed and the error occurs when viewing the model, is this a problem with Visual Studio and nont necessarily Anlaysis Services?
Thanks in advance.
Nick
Good morning,
Well... As I said in other topics, I'm doing a clustering plugin for text mining. I'm facing many problems and, with your help, solving them one by one.
First of all, thanks a lot again.
Well... I've made a clustering function that is actually working very well. But I'm exporting its results to a log file I use as an algorithm trace for debugging.
My clustering method returns a vector containing information of what cluster each register belongs. For instance:
vector[0] = 1 -> The register of index 0 belongs to cluster 1.
vector[1] = 9 -> The register of index 1 belongs to cluster 9.
vector[2] = 2 -> The register of index 2 belongs to cluster 2.
...
And so on.
But... I know that none of the Navigation methods receives a structure like this one discribed above. I only use it to log the results to debug the algorithm.
But how to pass this information (what register (or test case) belongs to what cluster) to the Navigation ?
Thanks a lot again, and any help will be very appreciated.
I have tried to process a data mining model that I have produce programmatically. I use the following code for processing
Private Sub BtProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtProcess.Click
' Now some different styles of processing
'Process mining structure first - don't process the models though
' ms.Process(ProcessType.ProcessStructure)
ms.Process(ProcessType.ProcessStructure)
DeclareBox.Text = "Process Data Mining Structure"
' Process the mining model only (because the structure has been processed above
' We could have said ProcessDefault without processing the structure first, though
mm.Process(ProcessType.ProcessDefault)
DeclareBox.Text = "Process Data Mining Model"
End Sub
I use the following code for data mining model and is correctly designed
Private Sub CreateMiningModel()
Dim mc As MiningModelColumn
mm = ms.MiningModels.Add(CbDatabase.Text & " miningmodelName") ', Utils.GetSyntacticallyValidID(miningmodelName, Type.GetType(MiningModel)))
mm.Algorithm = "Microsoft_Decision_Trees"
mm.AlgorithmParameters.Add("COMPLEXITY_PENALTY", 0.3)
mc = New MiningModelColumn("SUMCODE", "SUMCODE")
mc.SourceColumnID = ms.Columns("SUMCODE").ID
mc.Usage = "Key"
mm.Columns.Add(mc)
mc = New MiningModelColumn("STAFF_YES", "STAFF_YES")
mc.SourceColumnID = ms.Columns("STAFF_YES").ID
mc.Usage = "Input"
mm.Columns.Add(mc)
mc = New MiningModelColumn("D_G_OZODIS_IPER", "D_G_OZODIS_IPER")
mc.SourceColumnID = ms.Columns("D_G_OZODIS_IPER").ID
mc.Usage = "PredictOnly"
mm.Columns.Add(mc)
and I received the following error message. Any Ideas? What is wrong with this? Thank you in advance
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>test27</DatabaseID>
<MiningStructureID>test27 MiningStructureName</MiningStructureID>
<MiningModelID>test27 miningmodelName</MiningModelID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Mining Structure 'test27 MiningStructureName' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension 'test27 MiningStructureName ~MC-SUMCODE' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute 'OZOS NUM MANY' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[VISIT].[OZOS_NUM_MANY] AS [VISITOZOS_NUM_MANY0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Processing Dimension Attribute 'STAFF_YES' failed. 1 rows have been read.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT [VISIT].[STAFF_YES] AS [VISITSTAFF_YES0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported.. Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Processing Dimension Attribute 'D_G_OZODIS_IPER' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Errors and Warnings from Response
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported..
Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'OZOS NUM MANY' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'D_G_OZODIS_IPER' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Manolis
I am trying to write some DMX queries to create and populate a mining model as a SSMS analysis services project. I followed the following steps:
1. Create the mining model using a CREATE MINING MODEL ... query.
2. Followed by the - INSERT INTO MINING MODEL ... query, which fetches prediction data from another mining model to populate the mining model.
3. I now want to use this new model for prediction, which requires processing the mining model first. When I process the model, it throws the following error:
Error (Data mining): The 'XYZ_Structure' structure does not contain bindings to data (or contains bindings that are not valid) and cannot be processed.
Please suggest if I am making a mistake in the above procedure. I will appreciate all help in overcoming this issue.
Auxilliary question: How do I process the mining model programmatically?
Is it possible to group many time series into clusters by using the clustering algorithm of the SQL server 2005. The same question applies to "association rules" technique. Any examples?
Hi all,
I have a stored procedure that is called from a VB.NET application that takes an enormously long time to execute. In the QA it only takes 10sec but in the application it takes ages. The stored procedure is as follows:
PROCEDURE NAME IS SPTOPTWENTYUSERS
SELECT TOP 20 STRUSERNAME,SUM(INTBYTESRECVD) AS INTDOWNLOAD FROM TBLISAWEBLOGS
WHERE DTELOGDATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY STRUSERNAME
ORDER BY INTDOWNLOAD DESC
The code that runs it is as follows:
sSQLString = SPTOPTWENTYUSERS
Using cnn As New SqlConnection(GetPath)
Try
Dim cmd As New SqlCommand(sSQLString, cnn)
Dim dr As SqlDataReader
With cmd
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0
.Parameters.Add("@BEGINDATE", SqlDbType.DateTime)
.Parameters.Add("@ENDDATE", SqlDbType.DateTime)
.Parameters("@BEGINDATE").Value = dtpStartDate.Value
.Parameters("@ENDDATE").Value = dtpEndDate.Value
End With
cnn.Open()
dr = cmd.ExecuteReader
Any help on why this happens would be much appreciated.
thanks
Hi,I've a strange problem with a INSERT query. It's taking a long time toexecute. The format is like this :INSERT INTO table1SELECT ..FROM table2Executing the SELECT .. FROM table2 is taking 30 seconds. The resultis nothing: no records are selected.When i include the INSERT part it will take 12 hours to completeINSERT INTO table1SELECT ..FROM table2There's is an index on the table and when i delete it, it gives stillthe problem.Keh?Greetz,Hennie
View 1 Replies View RelatedHi,
It seems inserting records takes a relatively long time. My guess is it needs time to allocate disk space for the extra space needed. Assuming this is true, are there any DB settings that allow auto space allocation in bigger chunk? I am looking for something like "DB growth factor" or " Table growth factor"
Thanks for any info.
Dear friends,
Our package which at the time of normal execution takes 2-2:30 mins for fetching data on VPN with some select queries. But some times its job runs for hours and hours. What could be the exact reason behind it? I guess its queries are stuch somewhere, but not when we run from the BIDS or run the job manually.
Please help. Thanks.
Hi, all experts here,
Thank you very much for your kind attention.
I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?
Hope it is clear for your help.
I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi There,I have an update statement to update a field of a table (~15,000,000records). It took me around 3 hours to finish 2 weeks ago. After thatno one touched the server and no configuration changed. Untilyesterday, I re-ran it again and it took me more than 18hrs and stillnot yet finished!!!What's wrong with it? I can ran it successfully before. I have triedtwo times but the result was still the same.My SQL statement is:update [all_sales] aset a.accounting_month = b.accounting_monthfrom date_map bwhere a.sales_date >= b.start_date and a.sales_date < b.end_date;An index on [all_sales].sales_date is built successfully.A composite index on ([date_map].start_date, [date_map].end_date) isbuilt successfully.My server config is:SQL Server 2000 with Service Pack 3Windows 2000 with Service Pack 4DELL PowerEdge 6650 ServerDUAL XEON 1900MHz Processors2G RAM2G Page File on Drive C2G Page File on Drive DDELL Diagnostics on all SCSI harddisks were all PASSED.Any experts could simly give me a help????Thanks x 1,000,000,000
View 4 Replies View Related
Hi,
Its a common issue with reporting services, exporting a report with huge data to excel takes long time to render. Im facing the same issue. Trying to export a SSRS 2005 report to Excel 2003 takes very long time.
Problem 1:
The time taken to generate the excel report is pretty long (about 10 minutes) as the report runs to hundreds of pages. (The excel has about 30,000 rows and is ~15 MB)
Problem 2:
Once I open the excel and close it the size reduces to half of it. This is understood because of lot of characters values in the report that can be represented as single byte string, that€™s probably where Excel is making a difference. In Reporting Services it always write strings as 2-byte Unicode, but Excel will always try to compress to single byte when possible.
Am majorly concerned about the Problem 1. Any solutions would be highly appreciated. Thanks in advance.
I have a report which is fairly simple but takes a very long time..
It involves the incidents being counted by categories hence it has several Union All.
Also the report numbers are generetd through 2 tables hence within every Union All tehre is a left or an Inner join.
sample code:
SELECT
1 Sort_Order,
COUNT(*) AS Call_Count,
'Incident Resolved at Level 1' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
INNER JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE PROBLEM_STATUS = 'closed'
AND T2.THIRD_ASSIGNEE IS NULL
AND T2.THIRD_ASSIGNMENT IS NULL
AND T1.SECONDARY_ASSIGNEE IS NULL
AND HAL_FIRST_RES='t'
AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)
UNION ALL
-- Calls RESOLVED BY L2
SELECT
2 Sort_Order,
COUNT(*) AS Call_Count,
'Incidents Resolved at Level 2 or 3' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
LEFT JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE (HAL_FIRST_RES<>'t' OR HAL_FIRST_RES IS NULL)
AND PROBLEM_STATUS = 'closed'
AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)
UNION ALL
could you suggest what might be the reason why teh report churns for so long.
thanks,
kiran.
I have a vb.net application using report services that has a big delay when I set the parameters with which to call the report.
I create a new reporting.reportviewer.
I set the ReportServerCredentials.NetworkCredentials, ReportServerUrl, ProcessingModem, ReportPath and everything is fine.
When I call SetParameters with a very simple parameter set, I get a delay of between 0.5 and 2.5 seconds. That delay is very noticible to the users. Below is an extract of a sql profiler trace to a database showing the start time, end time, event class and data text of the sql. I've marked the area with the delay in red.
I have no idea what is happening at that time, but Is there anything I can do to get rid of that delay?
It seems that it could be the first time the my application has had to interface with reporting services.
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec ReadChunkPortion @ChunkPointer=0xFDFF126D000000006804000001000400,@IsPermanentSnapshot=1,@DataIndex=4148,@Length=8
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec ReadChunkPortion @ChunkPointer=0xFDFF126D000000006804000001000400,@IsPermanentSnapshot=1,@DataIndex=8,@Length=4100
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec sp_reset_connection
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec CreateSession @SessionID='5zz5gh2wgwfs2hf0qb0gh155',@ReportPath=N'/Symphony Reporting/report3',@Timeout=600,@AutoRefreshSeconds=0,@OwnerSid=0x010500000000000515000000F13F1E839A1DDABDC36D00302B060000,@OwnerName=N'MCR-SYSTEMS
hutchinson',@AuthType=1,@DataSourceInfo=0x0001000000FFFFFFFF01000000000000000C020000006F4D6963726F736F66742E5265706F7274696E6753657276696365732E50726F63657373696E67436F72652C2056657273696F6E3D392E302E3234322E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D3839383435646364383038306363393105010000004A4D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E52756E74696D6544617461536F75726365496E666F436F6C6C656374696F6E03000000106D5F636F6C6C656374696F6E42794944146D5F636F6C6C656374696F6E42795265706F7274146D5F636F6C6C656374696F6E427950726F6D70740303041C53797374656D2E436F6C6C656374696F6E732E486173687461626C651C53797374656D2E436F6C6C656374696F6E732E486173687461626C653D4D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E436F6C6C656374696F6E427950726F6D7074020000000200000009030000000A0A04030000001C53797374656D2E436F6C6C656374696F6E732E486173687461626C65070000000A4C6F6164466163746F720756657273696F6E08436F6D70617265721048617368436F646550726F7669646572084861736853697A65044B6579730656616C756573000003030005050B081C53797374656D2E436F6C6C656374696F6E732E49436F6D70617265722453797374656D2E436F6C6C656374696F6E732E4948617368436F646550726F766964657208EC51383F010000000A0A0B000000090400000009050000001004000000010000000906000000100500000001000000090700000004060000000B53797374656D2E477569640B000000025F61025F62025F63025F64025F65025F66025F67025F68025F69025F6A025F6B0000000000000000000000080707020202020202020232D4E1DE4550ED4AB904FB9304E177480507000000394D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F13000000046D5F6964066D5F6E616D650E6D5F6F726967696E616C4E616D650B6D5F657874656E73696F6E1B6D5F636F6E6E656374696F6E537472696E67456E63727970746564236D5F6F726967696E616C436F6E6E656374696F6E537472696E67456E63727970746564266D5F6F726967696E616C436F6E6E656374537472696E6745787072657373696F6E4261736564156D5F64617461536F757263655265666572656E6365086D5F6C696E6B49441D6D5F44617461536F757263655769746843726564656E7469616C734964096D5F73656344657363166D5F63726564656E7469616C7352657472696576616C086D5F70726F6D7074136D5F757365724E616D65456E63727970746564136D5F70617373776F7264456E63727970746564076D5F666C616773096D5F6D6F64656C4944136D5F6973456D626564646564496E4D6F64656C156D5F69734D6F64656C536563757269747955736564030101010707000103030704010707040300000B53797374656D2E477569640202010B53797374656D2E477569640B53797374656D2E4775696402544D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B43726564656E7469616C7352657472696576616C4F7074696F6E020000000202494D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B44617461536F75726365466C616773020000000B53797374656D2E4775696401010200000001F8FFFFFF0600000032D4E1DE4550ED4AB904FB9304E1774806090000000441525453060A0000000441525453060B0000000353514C090C0000000A00060D000000182F53796D70686F6E79205265706F7274696E672F4152545301F2FFFFFF06000000A814BB258EC9884888698BC39F85117601F1FFFFFF060000007C8BA03B04528840BACCDE1CC6E465BD091000000005EFFFFFFF544D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B43726564656E7469616C7352657472696576616C4F7074696F6E010000000776616C75655F5F00080200000003000000061200000039456E74657220612075736572206E616D6520616E642070617373776F726420746F2061636365737320746865206461746120736F757263653A0A0A05EDFFFFFF494D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B44617461536F75726365466C616773010000000776616C75655F5F0008020000000300000001ECFFFFFF060000000000000000000000000000000000000000000F0C00000098000000024E923E067F7AAB8735076784BE58B5E7FB39D61C8C5A39887A49C8F639783D2A0E4883F7901E6FE948DD535C1E2A84707D3071F31B5FDD7FCFC51278114BC810B48C8EC63D000F395FA8C28BFD18401221C78DDF7DB335425960CBB69E5823B62A418D46AE7120F1CCF3FCD3E3335E78DB72188BCF64457DB622592A9615775FC5C03758D12948BE507CCD16B2201C66A092A41F12B8D1D00F10000000190200000206050054000000020100048034000000440000000000000014000000020020000100000000041800FF00060001020000000000052000000020020000010200000000000520000000200200000102000000000005200000002002000054000000030100048034000000440000000000000014000000020020000100000000041800FFFF3F00010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000401000480340000004400000000000000140000000200200001000000000418001D000000010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000501000480340000004400000000000000140000000200200001000000000418001F000600010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000601000480340000004400000000000000140000000200200001000000000418001F00060001020000000000052000000020020000010200000000000520000000200200000102000000000005200000002002000054000000070100048034000000440000000000000014000000020020000100000000041800FF0106000102000000000005200000002002000001020000000000052000000020020000010200000000000520000000200200000B,@EffectiveParams=N'<Parameters>
<Parameter>
<Name>DataSource</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>AuthLvl</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>Home</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>SiteFilter</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>True</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>Select Required Site(s)</Prompt>
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>DateFilter</Name>
<Type>DateTime</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>True</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>Select Required Date(s)</Prompt>
<PromptUser>True</PromptUser>
</Parameter>
</Parameters>'
21/07/2006 13:29:48 377 21/07/2006 13:29:48 377 10 exec sp_reset_connection
21/07/2006 13:29:48 377 21/07/2006 13:29:48 377 10 exec ObjectExists @Path=N'/Symphony Reporting/report3',@AuthType=1
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec sp_reset_connection
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec GetSessionData @SessionID='5zz5gh2wgwfs2hf0qb0gh155',@OwnerSid=0x010500000000000515000000F13F1E839A1DDABDC36D00302B060000,@OwnerName=N'MCR-SYSTEMS
hutchinson',@AuthType=1,@SnapshotTimeoutMinutes=1440
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec sp_reset_connection
I have a table tblCustTrans which contains
custid int
transid int
startdate datetime
value int
the custid, transid and startid are composite primary key.
the table contains more than 10 million records. Now i want to fetch record for
select * from tblcusttrans where startdate > = 10/10/2006 10:00:000 and startdate <= 10/10/2006 11:00:000
This statement is taking more than 2 hours to fetch the data. is there a way to fetch the record with less time
Regards