July 29, 2010 at 10:01 am
Yes,
I think is a problem of index, Probably is not enough make the indexing process?
Forgive my rudeness, I thank you for your help and time investment :-).
July 29, 2010 at 10:11 am
There is no issue with the index. The SQL indexes do not get out of date like those you manually maintain, or index with something like FoxPro/dBase. They are built on the data, and they are up to date. If you have corruption, I could see an issue, but not likely always 2 hours of time.
It's possible that as you load it, you might be storing the times incorrectly, or loading incorrectly, so it appears you are missing data. Is the missing data from a time zone located xx hours away?
Or is the data in the table, but not in your query? My guess is that you might be structuring the query wrong. Can you query for a specific row that you know is there?
select *
from table
where starttime = 'xxx'
July 29, 2010 at 10:38 am
Ok, I ran some queries.
First, I ran the same query on the view and the table separately.
SELECT MAX (starttime) FROM MyView - get result 20100729182514
SELECT MAX (starttime) FROM MyTable -get result 20100729192251
I tried to get the result from MyView by the following query (with paramter that I got from MyTable).
SELECT [StartTime] FROM MyView where starttime = 20100729192251
Of course without result
Note that the difference between them is one hour, since there are not many events going into now.
July 29, 2010 at 10:49 am
First, please stop bolding everything. We can read it fine without adding that.
Second, what's the view code?
July 29, 2010 at 10:59 am
The view code is: (If you mean to my test)
SELECT Max (StartTime)
FROM [SynopSYS].[Report].[View_Report_Device_Events]
where starttime = 20100729192251
If you mean about in my program is:
SELECT
[DeviceType]
,[Devices]
,[ServiceType]
,[Services]
,[Users]
,[StartTime]
,[ClientIP]
,[StateType]
,[ServiceAction]
,[Reason]
,[Comments]
,[ProjectId]
,[DeviceTypeId]
,[DeviceId]
,[ServiceTypeId]
,[ServiceId]
,[StateTypeId]
,[AlarmNumber]
,[Severity]
,[ActionId]
,[AckAlarm]
,[EndAlarm]
,[UserId]
,[RelatedEvent]
,[EventId]
,[AckBy]
,[AckTime]
,[IpAck]
,[EndBy]
,[EndTime]
,[IpEnd]
,[StartupState]
FROM [SynopSYS].[Report].[View_Report_Device_Events]
where [StartTime] between 20100728181609 and 20100729195709
ORDER BY [StartTime]
July 29, 2010 at 11:04 am
I think he is (and I was going to ask) for is the view definition...
July 29, 2010 at 11:07 am
It's got to be in the conversions and the data. You're relying on implicit conversions here. You ought to be using quotes and ensuring the correct date is being used.
Or are these not datetimes?
July 29, 2010 at 11:20 am
The query is fine as well as values.
In fact, the DateTime is converted to a numeric value. Unfortunately, I have to use it until the next version.
Do you think if I was using the usual format, I get better performance?
Sorry if I bit pushy, but how can you explain to me that when there is not much data they enter I get results closer to the current time.
July 29, 2010 at 11:35 am
It's got to be something in the data. Your test view definition only returns one row, so it's not really a view test.
If you use < and < instead of between do things change?
What about
SELECT
[DeviceType]
,[StartTime]
FROM [SynopSYS].[Report].[View_Report_Device_Events]
where [StartTime] > 20100728181609
ORDER BY [StartTime]
Does all the data come back?
Actually is this a bigint column? If not, I think you're rolling over the 2B mark.
July 29, 2010 at 11:38 am
Here is why...
An bad index, should show up as a corruption issue and the data should be corrupt period, the corruption would not move to the last hours worth of data (or the last data page depending on what your clustered index might be). I have worked with SQL Server for a long time and have never seen nor have I heard of a 'laggy index'.
The index gets written to when data is inserted, updated or deleted as part of the same transaction there isn't a lazywriter or something else that deals with the index later.
Since to have the issue as you think it is (the index is getting updated late) you would either have....
-- A really long transaction is doing your insert and there is something in your standard environment that allows dirty reads that is overridden in the view (unlikely)
-- One of the indexes on one of the tables involved in your view is managing to keep the doubly linked list in the structure intact without error but is skipping a whole bunch of pages and then catching them later (really, really, really unlikely)
In general when someone says 'I am not getting the results I expect from a view' the process is...
Extract the syntax of the view
sp_helptext viewname
or just script it out.
Take the select that is used to define the view and run it outside the view and see what you get, do the same thing with the qualification on the Select that is used to define the view and see what you get.
Odds are there is another qualification inside you view that is causing the issue.
If however you want to go with the index theory, I would suggest dropping an recreating all of the related indexes. However I doubt it is going to do any good.
July 29, 2010 at 12:02 pm
Just an observation, but you say date time is formatted in yyyymmdd, but when you posted the query your between clause
was :
WHERE [StartTime] BETWEEN 20102209112223 AND 20102309112223
... which is yyyyDDmm. Your other posts and queries seem to be fine.
Might not be the cause of your problem, but could you clarify? Especially if you are converting datetimes to ints (not sure why you are doing that anyway), if you have the day before the month your query won't work.
July 29, 2010 at 12:09 pm
Hi Steve,
The column of date time defined as big int.
I tried to run your query and get with 2 min delay. It's better from 2 hours.
I need to test this query when the database get many values to insert, now is very late in my place.
July 29, 2010 at 12:09 pm
Hi Henry ,
When I insert any value to database I wrap the query with "begin transaction".
In my program I used with Entity Frame Work to deal with data.
In database there is delay between row in table and row that view represented.
July 29, 2010 at 12:10 pm
Hi Nevyn,
About the format that you treat, it is my mistake, should be 20100922112223.
Thanks
July 29, 2010 at 12:11 pm
How many inserts do you do within that transaction?
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply