July 28, 2010 at 3:38 am
Hi,
I am used with SQL server 2008 express edition sp1.
I get 200,000 events per 24 hours (10 evets per second).
I build indexes by Optimazer tool.
The problem:
At any time that I am interested to produce a report from view, I see the events of until two hours before.
In my opinion, the problem is resultant from it that the indices is not adequate to execute their work, This is right??
How I solve this problem?
Thank you to all.
Ido
July 28, 2010 at 7:58 am
Is this an indexed view? Also are you just doing a raw select on the view or do you have some sort of qualification on the select?
July 29, 2010 at 12:07 am
I not have Indexes on view, I have Indexes on my tables only.
Has you say, I do raw select on the view. I have only one sort column in my SQL select query, on TimeStamp column, and on the coulmn also.
In my opinion, I cannot create index view on SQL express edition. If this will solve my problem I'd love to know how to do it.
Thanks,
Ido
July 29, 2010 at 6:32 am
You can create indexed views in sql server 2008 Express edition
July 29, 2010 at 7:13 am
Yes, you're right. my mistake.:Whistling:
Does it solve my problem?
Can I see all the rows entering and not two-hour delay?
Anyway, I'll try it at the same time and write an answer.
I must to konw,
Is indexing process can delay the results of the query?
Thanks
July 29, 2010 at 7:30 am
Yes creating an index on a view can slow things up while the index is being created. There is also some overhead on write operations as well.
When you run just the raw query that is used for the view do you still have missing records?
July 29, 2010 at 7:36 am
Unfortunately yes, before created an index on the view.
July 29, 2010 at 8:45 am
Ok if just running the query that is used for view is also giving you the wrong results then I suspect that the view is not the issue.
Is the window of 'missing' data always the same (that is are you always missing 10,000 rows or the last 3 days of data)?
Have you run a consistency check on your server lately?
July 29, 2010 at 9:04 am
I am always running the same sql query command with diffrent Stamp Time, and I always get a missing data, such as - All data entered last two hours, I can not see them.
In fact, the data in the database but I do not receive them.
Example - In database I have data that entered until now (suppose 3:00 pm), I am running the query and get all data until 1:00 pm, and so on.
July 29, 2010 at 9:21 am
Any chance you can post the query?
July 29, 2010 at 9:38 am
Yes, of course, it is very simple query.
The parsing of the condition date time is:
YYYY MM DD mm dd ss
2010 07 29 18 22 33
SELECT [Projects]
,[ProjectId]
,[DeviceType]
,[DeviceTypeId]
,[Devices]
,[DeviceId]
,[ServiceType]
,[ServiceTypeId]
,[Services]
,[ServiceId]
,[StartTime]
,[StateType]
,[StateTypeId]
,[ClientIP]
,[Severity]
,[ServiceAction]
,[ActionId]
,[Users]
,[UserId]
,[Reason]
,[Comments]
,[StartupState]
,[EventId]
,[RelatedEvent]
,[AlarmId]
,[DeviceEventTime]
FROM [SynopSYS].[Report].[View_Report_Device_Events]
WHERE [StartTime] BETWEEN 20102209112223 AND 20102309112223
July 29, 2010 at 9:40 am
Ok humor me and give it a try with the date/times fully specked out ie
'01/01/2010 15:30:45.123'
July 29, 2010 at 9:41 am
Also when you just use the query that is in the view with the same qualification you get the same results right?
July 29, 2010 at 9:46 am
Yes, I get same result.
About Date Time it is legacy issue and world time zone problem.
July 29, 2010 at 9:51 am
Any chance this is all just a time zone issue, is always exactly a whole multiple of hours it is missing?
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply