Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone,

I need to create a service for sending information messages to customer which contract will end few days later. This sample is only one clause so i could add new criteria for another data tables; birth date, send welcome message after 2 days later, closing payment date for bill...

first practice is searching on database with this criterias. But I dont want to use this classic solution. Because I could set more than a hundred criteria to this service. So so many queries working on database and each query could not work ideal table Index.

have you ever build like this problems, and how did you handle it?

Thanks..
Gokhan
Posted
Comments
Tomas Takac 16-Jun-15 9:09am    
Sounds like you are trying to solve a performance problem which is not there.
Gokhan CETIN 16-Jun-15 11:19am    
Yes it will be performance problem if I do simple solution(searching on database tables). I'm trying to find best way instead of search database. For example; mapping event with criterias when its created.
Tomas Takac 16-Jun-15 15:15pm    
The question as you asked it is too vague. Show us your design, highlight where you think is the problem. You don't need to put everything into one questions, you can always ask follow-up questions.
virusstorm 16-Jun-15 11:51am    
Can you provide some table structures? When you say "classic solution", this is how all relational database work. Just because you use a column in a "WHERE" clause does not mean you need an index. There are also other techniques to include additional column information with an index so your query's performance doesn't suffer.

Also, the way you describe the other criteria that could be used, this makes me wonder how your tables are designed and if they are properly normalized.

I have built many systems that use "dynamic criteria", as we'll call this, and never once ran into performance problem that we could not resolve with proper database design, optimized queries, and physical architecture that could scale. This was all done with the "classical solution".

My suggestion is to build a proof of concept of your service (not go too crazy with elaborate design) and fill it with the amount of data you suspect you will have. Then run performance tests against it, you might be surprised just how much it can actually handle, but keep in mind the hardware you are using for test vs your production hardware.
Gokhan CETIN 17-Jun-15 5:30am    
I think I can not explain my problem :) My problem is not database performance. Simply; I want to create schedule job for x date on future which in some customer data.

Classic solution which i told is that;


example;
-Table Name: ScheduleWorker
-Columns: ID, Query, timeInterval,Action,IsActive

1.Create a windows service
2.Create a Timer for each ScheduleWorker Row
3.Set TimeInterval to Timer
4.Create Tick event for execute Query
5.Execute action for Query Results

Query could be Storedprocedure or text script...

If I create simple schedule worker, It always selecting data on database for each timer event fire. This solution could be use long timeIntervals. For example; work one time on a day(exp:02:00 am) for sending message for say to happy birthday which customer birth on this day.

Everything is ok for above senario.

Now I will try to explain my problem with new senario..

I have a new customer and customer signed new contract on today at 01:00pm. So I want to send welcome message after 5 day + 2 hour + 1 min from signed date(I know this sample was a little extreme :D). So if I use classic solution fro send message to my customer, I have to search database each minute for find this customer(where signdate = DATEADD(minute,-122,DATEADD(day,-5,GETDATE())))

So I dont want to this. I'm trying to learn that; does anyone using diffrent methods for executing this senario.

Actually best solution is that; add new row into Task Table When customer signed on contract. then always check just one table. But now; I have a current big CRM application and I cannot implement that insert codes on each action methods..

I'm sorry my english :) I tried to explain my problem :) I know it is very complicated model. I just try to find new ideas, and doing brain storming.

thanks again.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900